**Mortgage**
1. Assume Sam borrowed $120,000 for a home mortgage, to be repaid at 8% interest over 3 years with monthly payments. Answer the following questions:
a. How many monthly payments does Sam have to make for 3 years?
b. How much is the monthly payment?
c. How much is the interest payment for the third month?
d. 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 payments (Nper) = ??
- Monthly payment (pmt) = ??
- `pmt(rate, nper, pv, fv = 0, type = 0) = ??`
- Work out the monthly payment schedule for the next 3 years.
2. **Mortgage-1**
- How much is the monthly discount rate?
- 4.00%
- 8.00%
- 0.67%
- 1.00%
3. **Mortgage-4**
- Out of the second monthly payment, how much goes to the principal deduction?
- 2980.10
- 2960.36
- 2978.56
- 2999.97
---
**Retirement Planning**
1. Billy Petty, 56, just retired after 31 years of teaching. He has a husband and 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 a bank account earning 2 or 3% annually.
a. Bill has asked your advice on where to invest his lump-sum bonus and other accounts 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. His current monthly expenses total $5,800. He is not eligible for Social Security until age 62, when he will draw approximately $1,200 per month; however, he would rather defer drawing on Social Security until age 67 to increase his monthly benefit amount to $1,900.
2. The following questions are based on the above information.
a. **Retirement - 2**
- Ignoring his social security benefit, is the amount determined in question 1 sufficient to meet Bill’s 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 $3,000 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
3. **Retirement - 4**
a. Consider the information and assume Bill will keep the current expense at $5,800. He can draw $5,800 - $2,800 = $3,000 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
4. **Retirement - 5**
a. In the previous questions, can Bill cover the expenses if he claims a benefit at age 62?
b. If he claims social security benefits at age 67, he would get a monthly paycheck of $1,900. If he draws $5,800 - $2,800 = $3,000 till age 67, and then draws 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 a social benefit after age 67? Can he cover the expenses of $5,800?
- 5829.21
- 5818.21
- 5800.00
- 5780.63
