Question

READ BEFORE YOU START: Solve each of the following question using 3 methods: pricing formulas, a financial calculator, and Excel. When calculating using the pricing formula (e.g. Price of perpetuity P = C/r), please write the original formula and the steps where you plug in the number for each variable, and your final answers. When solving via a financial calculator, please indicate the inputs and output variable (e.g. PMT, N, PV, I/Y, and FV). When solving it using Excel, learn from the Excel functions file and print the results displayed in formula (Before printing, go to ‘Formulas’ panel, click ‘Show Formulas’ and then print the page). The results should have at least two columns: question number in column 1 and formula used in column 2 like below:

Question 1

=pmt(5%, 10, -100, 20, 0)

1. If you wish to accumulate $150,000 in 10 years, how much must you deposit today in a bank account that pays an annual interest rate of 12%?

Answer 1

Pricing formula:

Amount to be deposited today = Amount needed in future / (1 + r)ⁿ

= $150,000 / [1 + 0.12]¹⁰ = $150,000 / 3.1058 = $48,295.99

Financial Calculator:

N = 10;

I/Y = 12;

PMT = 0;

FV = 150,000;

Press CPT, then PV, which gives us -48,295.99

So, Amount to be deposited today = $48,295.99

Excel:

=PV(rate,nper,pmt,fv)

=PV(12,10,0,150000)

=-48,295.99

So, Amount to be deposited today = $48,295.99