Question

(a) What are the relationships of the present value and future value calculation of ordinary annuity and annuity due?

(b) Suppose you would like to buy a car using a loan that requires an annual payment of $1,000 for 20 years at the end of each year. If the annual interest rate is 3%, what are the present value of this car? What is the Excel function used for this calculation? What would the present value be if the loan requires a semi-annual payment of $500 at the beginning of each period? What is the Excel function for this annuity due?
(5 marks)
(c) An investment promises to pay $500 each year for 15 years. Calculate the future value of this annuity under the following circumstances:
• Annual payment at the end of each year
• Quarterly payment at the end of each quarter
• Monthly payment at the end of each month
• Daily payment at the end of each day
Create an Excel spreadsheet and specify the variable “Annual interest rate r” in Cell A1 and your choice of the rate value in Cell B1. In following rows, use the Excel function to calculate the future value of each of the above annuity in Column B and display the function used in Column C.
If the number of payment per year is n, what is the Excel function used to calculate the future value of the annuity investment above? Draw a line chart in Excel showing the relationship between future value of this investment (Y-axis) and number of payment per year (X-axis). Let the Y-axis start from (minimum of Y-axis equal to) the value when n=1.
What does the chart tell us?

Answer 1

a. What are the relationships of the present value and future value calculation of ordinary annuity and annuity due?

First let us understand what is ordinary annuity and annuity due

An ordinary annuity makes payments at the end of each period, like interest received on deposit or bond every six months.

An annuity due is when payments come at the beginning of each period like Rent received on property which is typically received at the beginning of each month

The formula is also different for both in present value (PV) and future value (FV)

FVOrdinary Annuity​=C*[(1+i)ⁿ −1/i ​]

PVOrdinary Annuity​= C*[1−(1+i)⁻ⁿ/ i ​]​

FVAnnuity Due​​= C*[ (1+i)ⁿ−1/ i ​]×(1+i)

PVAnnuity Due​=C*[1-(1+i)⁻ⁿ/ i ​]×(1+i)​

where:

C=cash flow per period

i=interest rate

n=number of payments​

b.

For Excel function we use PV and FV function to find the present value and future value of annuity

We use PV function in both case Annuity Due and Ordinary Annuity. But the “Type” feature of formula defines if it is ordinary annuity or annuity due. If we put 0 in type it means end of period and if we put 1 it means beginning of the period

Working in excel with formula in blue and PV function argument for understanding the function

А D formula 2 Annual Payment 3 Term (In years) 4 Interest rate 5 Period per year 1000 20 3% 2 semiannual payment 6 14,877.47 for annual payment =PV(B4,B3,-B2,0,0) 7 Present Value (Ordinary Annuity) 8 15,182.29 for semiannual payment =PV(B4/B5,B3*B5,-B2/B5,0,1) 10 Present Value (Annuity Due) Function Arguments PV E Rate Nper Pmt E = number = number = number = number = number Fv Type Returns the present value of an investment: the total amount that a series of future payments is worth now. Rate is the interest rate per period. For example, use 6%/4 for quarterly payments at 6% APR. Formula result = OK Help on this function eet97 Cancel Edit

c.Working in excel with formula in blue and FV function argument for understanding the function

D F G H I K L M N E 500 Amount Term in years 15 1 Annual interest rater 2 Annual Payment 3 Quarterly payment 4 Monthly payment 5 Daily payment 10% 15,886.24 =FV(B1,E2,-E1,0,0) 16,998.95 =FV(B1/4, E2*4,-E1/4,0,0) 17,269.60 =FV(B1/12, E2*12,-E1/12,0,0) 17,403.84 =FV(B1/365, E2*365,-E1/365,0,0) Chart Title 18,000.00 17,500.00 FV) 17,000.00 Function Arguments ? X Axis Title 16,500.00 16,000.00 15,500.00 Rate Nper Pmt = number = number = number = number = number 15,000.00 14 16.988.85 4 1728060 -Series1 15 15,886.24 16,998.95 17,269.60 17,403.84 16 Type 17 18 19 Returns the future value of an investment based on periodic, constant payments and a constant interest rate. Rate is the interest rate per period. For example, use 6%/4 for quarterly payments at 6% APR. 20 Formula result = Help on this function D OK Cancel

The chart shows that as the number of periods increases the future value also increases this happens because of the compounding factor. Where interest on interest is also calculated at the given rate of interest