Question

How to use excel function to solve this problem?

Bill and Cathy will be retiring in fifteen years and would like to buy an Italian villa. The villa costs $500,000 today, and housing prices in Italy are expected to increase by 6.5% per year. Bill and Cathy wants to deposit an equal amount at the end of every year so that they can buy this villa in 15 years.

Answer 1

Answer : First we will use Excel Function of FV to calculate Future Value of Annuity

=FV(rate,nper,pmt,pv)

where

rate is the rate of inflation i.e 6.5%

nper is the number of years i.e 15

pmt is 0

pv is the value of villa today 500000

=FV(6.5%,15,0,-500000)

Therefore Future Value will be 1,285,920.50.

Now we will calculate Equal Annual Deposit by using PMT function of Excel:

=PMT(rate,nper,pv,fv)

where rate is the rate of interest i.e 10% (Assumed)

nper is the number of payments i.e 15

pv is 0

Fv is future value of Annuity (calculated above)i.e 1285920.50

=PMT(10%,15,0,-1285920.50)

Therefore Annual Deposit is $40472.78 or $40473