Question

1) Explain the process of calculating the future value of an ordinary annuity using both Excel and a financial calculator, and provide an example.

2) Explain the process of calculating the present value of an ordinary annuity using both Excel and a financial calculator, and provide an example.

3) Explain the terms annual percentage rate (APR) or nominal interest rate, and effective annual interest rate (EFF% or EAR).

4) Explain what an amortization schedule is and how it is calculated.

Answer 1

Answer 1.

Calculating Future Value of an Ordinary Annuity using Excel:

=FV(RATE, NPER, PMT, PV)

TIME=NPER

Calculating Future Value of an Ordinary Annuity using Financial Calculator:

Type and figure and click on:
N = number of years.
1/Y = Interest rate.
PMT= periodic payments.
FV= future value (=0).

Then Press, CPT+PV in order to arrive at the final result.

Example of Annuity Future Value:

Answer 2.

Calculating Present Value of an Ordinary Annuity using Excel:

=PV(RATE, NPER, PMT)

TIME=NPER

Calculating Future Value of an Ordinary Annuity using Financial Calculator:

Type and figure and click on:
N = number of years.
1/Y = Interest rate.
PMT= periodic payments.
FV= future value (=0).

Then Press, CPT+PV in order to arrive at the final result.

Example of Annuity Present Value:

Answer 3.

Annual percentage rate (APR) or nominal interest rate:

An annual percentage rate (APR) or nominal interest rate refers to the annual rate charged for an amount borrowed or earned by way of an investment. APR is expressed in terms of percentage that shows the actual yearly funds' cost over the loan period. This may include any fees, additional costs associated with transaction however, it does not consider compounding into account.

APR is caculated using the following formula:

APR= ((((Fees+Interest)/Principal)/n)* 365) * 100

where:Interest=Total interest paid over life of the loan

Principal=amount of loan

n=Number of days in loan term​

Borrowers evaluates the APR figure at the time of comparing credit cards or mortgage rates.

Effective Annual Interest Rate (EFF% or EAR):

Effective annual interest rate refers to the rate of interest that is actually earned or paid on any loan, investment, or some other financial product as a result of compounding over a given period of time. It is also known as effective interest rate, effective rate or annual equivalent rate.

EAR is caculated using the following formula:

Effective Annual Interest Rate= ((1+(i/n))^n)- 1

where,

i=Nominal interest rate

n=Number of periods​

Effective annual interest rate is a major financial concept as it is employed to carry out comparison between different product which includes lines of credits, loans, or investment products like deposit certificates which computes compounded interest differently.

Answer 4.

Amortization is the term used for paying off a particular debt with fixed regular installments on a fixed repayment schedule over set period of time.

Each and every entry made in the amortization table is one and single payment against the loan. These payments are broken down into two parts first, the amount going towards the principal payment of loan and second, the amount going towards interest. At the starting phase of loan’s term, when remaining principal is still high, most part of monthly payments go towards interest. However with the decrease in principal, the percentage of every payment going towards interest gradually decreases, and percentage going towards principal rises or increases.

Calculation of Amortization Schedule:

We need the following information to construct Amortization Schedule:

1. Borrowed sum

2. Loan term

3. Monthly payment

4. Monthly rate of interest

For the payment of first month, multiply balance of loan by the monthly rate of interest. It results in the interest amount for the payment of first month. Subtract this amount from monthly payment to know about the amount of first payment went toward paying the balance of loan. Subtract this amount of principal payment from loan balance to arrive at new loan balance for the second month and then repeat this entire  process for the life of mortgage.

Example of Amortization Schedule:

X is buying a house for $200,000. He made 20 percent down payment of $40,000 and required to take out mortgage for $160,000. He wants 30-year mortgage (360 months) at 4 % rate of interest. His estimated monthly payment would be $763.86 and first lines of his amortization schedule will appear as follows:

Amortization Schedule

Date	Payment	Principal	Interest	Total Interest	Balance
Month 1	$763.86	$230.53	$533.33	$533.33	$159769.47
Month 2	$763.86	$231.30	$532.56	$1,065.90	$159,538.17
Month 3	$763.86	$232.07	$531.79	$1,597.69	$159,306.10
Month 4	$763.86	$232.84	$531.02	$2,128.71	$159,073.25
Month 5	$763.86	$233.62	$530.24	$2,658.96	$158,839.63

Answer 2

1) Future Value of an Ordinary Annuity

Definition: The future value (FV) of an ordinary annuity calculates how much a series of equal periodic payments will be worth in the future, given a fixed interest rate.

Using Excel:

• Formula:

  
  

  =FV(rate, nper, pmt, [pv], [type])

• rate = interest rate per period

• nper = number of periods

• pmt = payment per period

• [pv] = present value (optional, default = 0)

• [type] = 0 (ordinary annuity, payments at end of period)

Example:

• $1,000 annual payment for 5 years at 5% interest:

  excel

• =FV(5%, 5, -1000) →

Using a Financial Calculator:

1. Clear all previous entries.

2. Input:

• N = 5 (years)

• I/Y = 5 (interest rate)

• PMT = -1000 (payment, negative for outflow)

• PV = 0 (no initial lump sum)

3. Press CPT → FV → $5,525.63

---

2) Present Value of an Ordinary Annuity

Definition: The present value (PV) calculates what a series of future annuity payments is worth today.

Using Excel:

• Formula:

  excel

• =PV(rate, nper, pmt, [fv], [type])

• fv = future value (optional, default = 0)

Example:

• $1,000 annual payment for 5 years at 5% interest:

  excel

• =PV(5%, 5, -1000) →

Using a Financial Calculator:

1. Clear all entries.

2. Input:

• N = 5

• I/Y = 5

• PMT = -1000

• FV = 0

3. Press CPT → PV → $4,329.48

---

3) APR vs. EAR

• Annual Percentage Rate (APR):

• The nominal interest rate per year, ignoring compounding.

• Example: A 12% APR compounded monthly = 1% monthly rate.

• Effective Annual Rate (EAR or EFF%):

• The actual annual rate including compounding.

• Formula:

  $$EAR={(1+\frac{APR}{m})}^m-1$$

  Where $m$ = compounding periods per year.

• Example: 12% APR compounded monthly →

  $$EAR={(1+\frac{0.12}{12})}^{12}-1=12.68\mathrm{\%}$$

---

4) Amortization Schedule

Definition: A table showing the breakdown of each loan payment into principal and interest over time.

Calculation Steps:

1. Monthly Payment (PMT):

• Use Excel (=PMT(rate, nper, pv)) or a financial calculator.

• Example: $100,000loanat6$599.55/month**.

2. Interest & Principal Breakdown:

• Interest = (Remaining Balance × Monthly Rate)

• Principal = (PMT − Interest)

3. Update Balance:

• New Balance = Previous Balance − Principal

4. Repeat for all periods.

5. 
   

Example (First 3 Months):

Month	Payment	Interest	Principal	Remaining Balance
1	$599.55	$500.00	$99.55	$99,900.45
2	$599.55	$499.50	$100.05	$99,800.40
3	$599.55	$499.00	$100.55	$99,699.85

Key Takeaway: Early payments are mostly interest; later payments shift to principal.