Question

Please explain how to answer the following question using Excel:

How much would Mel be willing to pay for an annuity that will pay Mel $150,000 a year for twenty years, assuming a 5.25% rate of interest? (Treat this as an ordinary annuity.)

Answer 1

Given

Annuity payment P=$150000

Interest rate r=5.25%

Number of payments N=20

Amount willing to pay = Present worth of annuity PV

PV=P*(1-(1+r)^-N)/r

PV=150000*(1-(1+5.25%)^-20)/5.25%=$1830333.39

In excel we will use present value function PV

=PV(Rate,Nper,Pmt,[FV],[Type])

=PV(5.25%,20,150000,0,0)=$1830333.39

we will use Type=0 because it is ordinary annuity.

Rate	5.25%	Interest rate
Nper	20	Number of payments
Pmt	150000	Payment per period
FV	0	Future value of any lump sum payment