HW#3

 

Create a spreadsheet to solve the following problems.  I want to see the formulas (FV, PV, PMT, NPV, IRR, NPER, and EXP where appropriate). 

 

Each student must submit his or her own unique file. 

Please number your answers clearly in bold or highlighted.

 

 

Future Value (10 points)

 

1.         If I won a lump-sum lottery today which gave me $45,000 and I decided to put it into a 5 year investment earning an annual rate of 4%, how much would it be worth at the end of 5 years?

 

2.         What if I decided to put my winnings in the same investment for 10 years?

 

 

Present Value (10 points)

 

3.         If I won a lump-sum lottery which promises to give me $45,000 5 years from now and the annual interest rate is 2%, how much would it be worth today (present value)?

 

4.         What if the rate is 4%?

 

 

Annuities (10 points)

 

5.         What is the present value of a car payment of $299 per month for 5 years with a 3.50% annual rate?  (In other words, how much cash would it take today to buy the car outright instead of making monthly payments?)

 

6.         What is the future value of a payment of $299 per month for 5 years with a 3.50% annual rate?  (In other words, if you had put the monthly payments in the bank earning 3.50%, how much would it be worth at the end of 5 years?)

 

 

Annuity Payment and Number of Periods (20 points)

 

7.         If I bought a house for $350,000 and mortgaged 80% of the cost using a 30-year loan at a fixed annual rate of 4.0%, what would my annual mortgage payment be? 

 

8.         What would my monthly mortgage payment be?

 

9.         If you double your monthly mortgage payments from above, how long will it take to pay off your mortgage in months?

 

10.        Why does doubling your monthly mortgage payment reduce the length of the loan by more than half?

 

 


Deferred Annuities (25 points)

 

Suppose your 7 year old daughter has announced that she would like to attend college.  You believe it will cost $22,000 per year, for 4 years, for her to attend state college 11 years from now.  You are giving yourself 11 years to save and assume you will earn 6% annually (inflation-adjusted) on your savings. 

 

11.        What is your savings goal (what must the account be worth 11 years from now)?

Note: tuition payments will be made at the beginning of each year (type = 1).

 

12.        If you want to fund that savings goal with a one-time lump-sum today, how much would you need?

 

13.        If instead of a one-time lump-sum, you contribute at the BEGINNING of each year, what must your annual payments be to reach the goal?

 

14.        If instead of a one-time lump-sum, you contribute at the END of each year, what must your annual payments be to reach the goal?

 

15.        What must your annual payments (at the end of each year) be if grandma decided to open the account with $5,000 today?

 

 

Un-Even Cash Flows (20 points)

 

If you want to contribute annually to your IRA and you want your contribution to grow over time in the following way:

 

Year

Cash Flow

1

-1700

2

-2500

3

-3000

4

-4250

5

-4500

Interest Rate

7%

 

16.        What is the present value of the cash flows?

 

17.        What is the future value of the cash flows?

 

18.        What is the Internal Rate of Return on these cash flows:

 

Year

Cash Flow

0

-14500

1

2800

2

3000

3

3500

4

4000

5

5000

 

19.        If you have a choice between the investment in question #18, and a less risky project offering an annual rate of return of 11%, which would you choose?

 


Continuous Compounding (5 points)

 

20.        If you borrowed $10,000 from a credit card which compounds interest continuously, and charges an annual rate of 23%, what would the balance (future value) be if you haven’t paid anything towards the loan after 3 years?

 

 
 
 
 
    • 6 years ago
    HW#3
    NOT RATED

    Purchase the answer to view it

    blurred-text
    • attachment
      hw3-s15.xlsx