Question

Please create an Excel file with the calculated answers to the questions to the Excel homework for this week. The questions are listed in the attached PDF file.  

Please also go over the Excel file attached to this assignment in order to familiarize yourself with the different ways Excel can be used to solve Time Value of Money problems when multiple cash flows are involved. There are three worksheets in the Excel file. This Excel file with examples is just that: a file to show you some examples of using Excel to solve TVM problems. Do not confuse this posted Excel file with the separate Excel file you need to create and submit with the answers to this week's homework questions. You should examine the formulas in the posted Excel file and use them as a guide when you create your new Excel file for submission.

What is the implied discount rate in an annuity product that costs $50,000 today and
pays the owner twenty annual payments of $3,000? hint: you need to use the Excel RATE
function and enter the payment as a negative number.

2. How many months will it take to pay o a $10,000 balance on a credit card that has
an APR of 18%, assuming you charge no additional money on the card and make regular
monthly payments of $175? hint: you need to use the Excel NPER function and enter the
payment as a negative number.

3. What is the monthly payment on a $200,000 30-year 6% xed rate mortgage?

4. How much money will you have in twenty-ve years if over that period you make monthly
deposits of $300.00 into an investment account that earns an APR of 6% over that period?

5. Assuming a discount rate of 7%, how much money should you be willing to pay for an
investment that will generate annual cash ows of $12,000 per year for ten years?

Answer 1

1]

Implied discount rate is calculated using RATE function in Excel :

nper = 20 (total number of annual payments)

pmt = -3000 (Annual payment)

pv = 50000 (Amount paid today for annuity)

RATE is calculated to be 1.80%

2]

The number of months to pay off the credit card is calculated using NPER function in Excel :

rate = 18%/12 (converting APR into monthly rate)

pmt = -175 (Monthly payment)

pv = 10000 (Balance outstanding today)

NPER is calculated to be 130.70

3]

Monthly loan payment is calculated using PMT function in Excel :

rate = 6% / 12   (converting annual rate into monthly rate)

nper = 30*12 (30 year loan with 12 monthly payments each year)

pv = 200000 (loan amount)

PMT is calculated to be $1,199.10

4]

Money you will have in twenty years is calculated using FV function in Excel :

rate = 6% / 12   (converting annual rate into monthly rate)

nper = 20*12 (20 years of deposits with 12 monthly deposits each year)

pmt = -300 (monthly deposit)

FV is calculated to be $138,612.27

5]

Money you should be willing to pay is calculated using PV function in Excel :

rate = 7% (discount rate)

nper = 10 (number of years)

pmt = -12000 (yearly payment)

PV is calculated to be $84,282.98

PV is calculated to be $84,282.98