Question

Assume that you want to buy a new car in 5 years, and that the price of the car will be $30,000. (Assume all cash flows occur at the end of the period throughout all of the Excel problems throughout our entire course.) Use Excel time value of money functions to solve the problems outlined below (one or more of these: PV, FV, RATE, NPER, PMT) (This question doesn't require a text box with written answers, but you need to clearly label your answers to parts a, b, and c.) a. How much do you need to deposit in an account today, if you want to have $30,000 in the account in 5 years, assuming the account earns 8% interest rate annually? b. If you deposit $22,000 in the account today, what rate of interest would you need to earn annually in order to have exactly $30,000 in the account in 5 years? C. Assuming your account earns 0.5% interest rate every month, and that you make an initial deposit of $10,000 today, how much do you need to deposit every month in your account in order to have exactly $30,000 in 5 years? (Hint: use the PMT variable to represent the monthly deposit; make sure you use the number of months for the number of periods.)

Answer 1

Target to have $30000 in 5 year from now.

a). Let us suppose, P was deposited in account today.

P is the Present value of $30000 in 5 year at 8% interest rate annually

So, P = PV(8%, 5,0,30000) (using PV(rate,nper,pmt,[fv],[type]) formula in excel)

P= $20417.50

b). If $22000 was deposited today at rate r% per annum, for 5 year

Using rate(nper, pmt,pv,[fv],[type])

r = rate(5,0,-22000,30000) = 6.40% annually

c). Given that, $10000 is invested today, at 0.5% monthly

Let A is deposited every month for next 60 months. Final amount is $30000

So, PV = $10000

rate = 0.5%

nper = 30 months

FV = 30000

Using PMT(rate,nper,pv,[fv],[type]) in excel

A = PMT(0.5%, 60,-10000,30000) = $236.66