Part 2 Mortgage

Now that you have enough money for a down payment on the house that you want to buy, its time to look into getting a mortgage. You have agreed to buy the house for $125,000 and to make a 20% down payment on the house. You have also found a bank that will finance the remaining balance of the house.

You must now choose between a 20-year mortgage and a 30-year mortgage. The 20-year mortgage has an annual interest rate of 4.52% and the 30-year mortgage has an annual interest rate of 4.96%. Both rates are compounded monthly.

What are the advantages and disadvantages of the 30-year and the 20-year mortgages?

We can answer this question by creating an Excel spreadsheet that details the payments made each month as well as how much of the payment goes toward interest, and how much goes toward the balance of the home loan. In effect, we will create an amortization schedule for each type of mortgage, and compare the results.

1. First, we will create an amortization schedule for the 30-year mortgage. At the top of the spreadsheet, set the parameters for this situation. First, the present value of the mortgage (PV). This is the cost of the house minus the down payment that you have already made. The other parameters are the annual interest rate (r) and the number of years to pay off the mortgage (t).

2. Set up the following 6 columns for the 30-year mortgage:

Period Keeps track of the number of periods, in this case months, of the loan. Start with Period 0.

Years Keeps track of the number of years of the mortgage.

Payment Gives the amount of your monthly mortgage payment. This amount will stay constant throughout the entire 30 years. To calculate the payment amount, we could use the present value equation from Section 3-4, but instead we will use the Excel function PMT. The PMT function calculates the monthly payment given the parameters:

rate This is the interest rate per month.

nper This is the total number of periods of the mortgage.

pv - This is the present value or initial amount of the mortgage.

The payment amount is automatically given as a negative value. In order to change this to a positive number, simply put a negative sign in front of the PMT.

Interest Paid Gives the amount of the monthly payment that goes toward interest on the loan. This can be calculated by multiplying the previous unpaid balance by the interest rate per period.

Equity Paid - Gives the amount of the monthly payment that goes toward paying off the loan itself. This can be calculated by taking the monthly payment and subtracting the interest paid. The leftover is the equity paid.

Unpaid Balance Gives the amount of the mortgage that is still unpaid. Only the equity paid goes toward the unpaid balance. It will decrease over the life of the loan until it becomes $0 when the loan is paid off.

3. Fill out the spreadsheet for 30 years when the Unpaid Balance of the loan should be $0. Calculate the total amount of the monthly payments, and well as the total amount of interest paid and equity paid.

4. Repeat this process by creating another amortization schedule for the 20-year loan.

5. Compare your results for the 30-year and 20-year mortgages by making a table with the Total Payments, and Total Interest Paid for the 30-year and 20-year mortgages.

Part 3 Retirement Plan

Now that you have a house, its time for you to plan for retirement. Your plan is to take a certain amount from your salary at the end of each year and invest it in a 401(K) mutual fund. Then when you get sick of your job and want to retire, you will have a fund that you can withdraw from each year to live on.

Lets assume you want to retire at age 60 and your life expectancy is 90.

Suppose that your 401(K) mutual fund yields an interest rate of 6.6% APR compounded annually. Determine for yourself how much money you want to invest in the 401(K) each year. This is the payment that you will make at the end of each year. Also determine at what age you want to start paying into the 401(K) investment plan. Finally, determine how much money you will need to withdraw from the fund each year to live on when you are retired. An example could be to invest $2000 each year starting at age 21 and withdraw $20,000 each year during retirement.

What must you invest each year during the accumulation phase (now until retirement) so that you will have just enough money to live on during retirement, the payout phase?

We will answer this question by creating an Excel spreadsheet that keeps track of how much money is in your 401(K) during the accumulation phase and during the payout phase (during retirement).

1. First set up your parameters at the top of the spreadsheet. The parameters should be: the annual interest rate (r), the age that you start making payments into the 401(K), the amount of the payment at the end of each year, and the amount that you will withdraw each year during retirement. (You decide how much you will need each year in retirement.)
2. Next we will set up a spreadsheet that keeps track of the account balance during the years that you are adding money to the account. Create the following 4 columns:

Age Keeps track of your age.

Payment Keeps track of how much you invest in the 401(K) at the end of each year. This value will stay constant until the age when you retire. Assume that you make your first payment at the end of year 1.

Interest Keeps track of how much interest is earned during each year. Since you make your annual payments at the end of the year, the interest earned during the first year will be $0.

Balance Keeps track of the account balance in the 401(K) at the end of the year.

3. Fill out the spreadsheet for a few years to see how the account balance grows. Assume that you continue to make payments until the stated retirement age.

4. Now set up another spreadsheet to the right that keeps track of the account balance during the retirement years that you are withdrawing. Set up the following 4 columns:

Age Keeps track of your age -starting with the retirement age. Assume that your first withdrawal takes place at the end of the last year that you worked.

Withdrawal Keeps track of how much you take out of the 401(K) at the end of each year. This value will stay constant until the stated life expectancy age.

Interest Keeps track of how much interest is earned during each year. Since you already calculated the interest earned during your last year of work, you do not need to add any interest for the first row of this spreadsheet.

Balance Keeps track of the account balance in the 401(K) at the end of the year.

5. Fill out this spreadsheet until the life expectancy age. Your goal is to have zero dollars balance at the end.

6. Adjust each spreadsheet by changing the payment amount and withdraw amount so that you will have just enough money in your account to live on during the payout phase.

7. Calculate the total amount that you paid into the account, the total amount that you withdrew from the account, and the total interest made during the lifetime of the account, and compare these values.