Reference no: EM131431877
Excel Project Assignment- Financial Planning
Part 1 - CD Investment
Suppose that you have $10,000 that you would like to use toward the purchase of a house. The houses that you are interested in buying have an average value of $135,000. In order to purchase a house like this, you would like to make a down payment that is 20% of the value of the house. Since you do not currently have enough money for the down payment, you decide to invest your $10,000, and wait until you have enough money from your investment for the down payment. The best investment that you could find is a CD that pays 4.8% APR compounded quarterly.
How long will it take until you have enough money for the down payment?
We could solve this problem using the compound interest formula from Section 3-2. Instead, we will create an Excel spreadsheet that will track the account balance for each period.
1. At the top of spreadsheet, set the parameters for this situation: Principle (P) and Annual Interest Rate (r).
2. Set up the following 4 columns:
Period - Keeps track of the number of compounding periods. Start with Period 0.
Years - Keeps track of the number of years that your money is in the CD.
Interest Earned - Keeps track of how much interest is earned each period.
Account Balance - Keeps track of how much is in your account at the end of each period.
3. Fill out the spreadsheet until you have enough in the account for a down payment on the house. Calculate the total amount of interest earned, and compare it to the final account balance.
4. Create a column graph that shows the growing account balance over time until you have enough for the down payment. The horizontal axis should be Years, and the vertical axis should be Account Balance. To do this, you need to click on "Select Data" located in the Design tab of Chart Tools. Be sure to label your graph as well as labeling the horizontal and vertical axis.
Part 2 - Mortgage
Now that you have enough money for a down payment on the house that you want to buy, it's time to look into getting a mortgage. You have agreed to buy the house for $135,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.76%. 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, it's 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.
Let's assume you want to retire at age 62 and your life expectancy is 90.
Suppose that your 401(K) mutual fund yields an interest rate of 4.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.