Question

You want to buy a house in 15 years that costs $300,000 now. Inflation is 3%. How much do you need to save each year to be able to buy the house (for cash, without a mortgage,) if you can earn 6% (compounded annually) on your savings? (Answer to the nearest cent.)

How do I solve this in excel?

Answer 1

Calculation of cost of house in 15 years:

PV = $300,000
Rate = 3%
Nper = 15
PMT = 0

Future value can be calculated by using the following excel formula:
=FV(rate,nper,pmt,pv)
=FV(3%,15,0,-300000)
= $467,390.22

Cost of house in 15 years = $467,390.22

Calculation of amount of annual savings:

Rate = 6%
Nper = 15
FV = $467,390.22
PV = 0

Amount of annual savings can be calculated by using the following excel formula:
=PMT(rate,nper,pv,fv)
=PMT(6%,15,0,-467390.22)
= $20,080.38

Amount of annual savings = $20,080.38