Q.1

Mortgage

Assume Sam borrowed $120,000 for a home mortgage, to be repaid at 8% interest over 3 years with monthly payments.

• How many monthly payments does Sam has to pay for 3 year?
• How much is the monthly payment?
• How much is the interest payment for the third month?
• How much interest is paid over the life of the loan?

Hint:

• Study the class excel example on a mortgage payment.
• Annual percentage rate (APR) = ??
• Monthly discount rate (rate) = ??
• Number of payment (Nper) = ??
• Monthly payment (pmt) = ??
• pmt(rate, nper, pv, fv =0, type= 0) = ??
• work out the monthly payment schedule for the next 3 years.

The following questions will be based on the above information.

1. Mortgage-1

-How much is the monthly discount rate?

4.00%

8.00%

0.67%

1.00%

-----------------------------

2. Mortgage-4

Out of the second monthly payment, how much goes to the principal deduction?

2980.10

2960.36

2978.56

2999.97

------------------------------------------------------------------------------------------

Q2.

Retirement planning

Billy Petty, 56, just retired after 31 years of teaching. He is a husband of two children who are still dependent.

He receives a $150,000 lump sum retirement bonus and will receive $2,800 per month from his retirement annuity.

He has saved $150,000 in a 403(b) retirement plan and another $100,000 in other accounts. His 403(b) retirement plan is invested in mutual funds, but most of his other investments are in bank account earning 2 or 3% annually.

Bill has asked your advice in deciding where to invest his lump-sum bonus and other account now that he has retired. He also wants to know how much he can withdraw per month, considering he has two children and a nonworking spouse. Because he has children, his current monthly expenses total $5,800. He is not eligible for Social Security till age 62, when he will draw approximately $1,200 per month; however, he would rather defer drawing on Social Security until 67 to increase his monthly benefit among to $1,900.

The following questions are based on the above information.

1. Retirement - 2

Ignoring his social security benefit, is the amount determined in question 1 sufficient to meet Bills current monthly expenses (keep in mind that he will receive a pension of $2,800 per month)?

If not, how long will his retirement savings last if his current expenses remain the same?

Hint:

• Draw 3000 monthly till n years, can he reach age 86?
  • NPER(rate = ?, nper = ?, pmt = 5800 - 2800, pv = -400,000, fv = 0, type = ??)
  • n = nper / 12

16.25

30.55

20.25

30.00

-------------------------------------------------

2. Retirement - 4

Consider the information and assume Bill will keep the current expense at 5800.

He can draw 5800-2800 = 3000 till age 62, and then draw x amount till age 86 (another 24 years).

Hints:

We can first solve the ending balance at the age of 62.

• Ending amount at age 62 = FV(rate = ??, nper = ??, pmt = -3000, pv = 400,000, type = 0)

Then, we can solve x.

• x = PMT(rate = ??, nper = ??, pv = (Ending amount at age 62), fv = 0, type = 0)

The total monthly income after age 62 till age 86 = x + 1200.

How much is the total monthly income?

5720.94

5278.90

5902.35

5800.00

-------------------------------------------------------------

3. Retirement - 5

In the previous questions, can Bill cover the expenses if he claims benefit at age 62?

If he claims social security benefit at age 67, he would get a monthly paycheck of 1900.

If he draws 5800-2800 = 3000 till age 67, and then draw y amount till age 86 (another 19 years).

The total monthly income after age 67 till age 86 = y + 1900.

How much is the total monthly income if he claims social benefit after age 67?

Can he cover the expenses of 5800?

5829.21

5818.21

5800.00

5780.63