You are buying a house and trying to decide when you want to mortgage to mature. You are going to borrow $170,000. Your credit is good, so you will get the lowest rates available. You have two choices. You can finance for 30 years at an interest rate of 3.85% per year, or for 15 years with an interest rate of 3.15% per year. Set up a full amortization schedule for each of these options to help you make your decisions (so you will have two worksheet tabs in the file, one for the 15-year loan and one for the 30-year loan).
In class, we used three methods to calculate the payment that is required each month. You must use two of these methods in the assignment (use a different method for each of the two loan tabs). ( Methods: PMT Function, Self- Coded, PPMT IPMT)
One a third tab, calculate the extra principal payment needed each month if you take the 30-year loan but decide to pay it off in 15 years (so you owe nothing after 180 monthly payments). You can use either of the methods we used in class to calculate the extra principal.
Calculating the PMT function to find the annual payment -
PMT function --> PMT (rate, nper, pv, [fv], [type])
where, rate is the rate of interest, nper is the number of periods, PV is the value of loan, fv is the remaining balance at end of loan (0 here)
Loan Amount | 170000 |
Payment Period | 30 |
Interest Rate | 3.85% |
Year | Annual Payment |
1 | 9652.86 |
2 | 9652.86 |
3 | 9652.86 |
4 | 9652.86 |
5 | 9652.86 |
6 | 9652.86 |
7 | 9652.86 |
8 | 9652.86 |
9 | 9652.86 |
10 | 9652.86 |
11 | 9652.86 |
12 | 9652.86 |
13 | 9652.86 |
14 | 9652.86 |
15 | 9652.86 |
16 | 9652.86 |
17 | 9652.86 |
18 | 9652.86 |
19 | 9652.86 |
20 | 9652.86 |
21 | 9652.86 |
22 | 9652.86 |
23 | 9652.86 |
24 | 9652.86 |
25 | 9652.86 |
26 | 9652.86 |
27 | 9652.86 |
28 | 9652.86 |
29 | 9652.86 |
30 | 9652.86 |
Loan Amount | 170000 |
Payment Period | 15 |
Interest Rate | 3.85% |
Year | Annual Payment |
1 | 15130.05 |
2 | 15130.05 |
3 | 15130.05 |
4 | 15130.05 |
5 | 15130.05 |
6 | 15130.05 |
7 | 15130.05 |
8 | 15130.05 |
9 | 15130.05 |
10 | 15130.05 |
11 | 15130.05 |
12 | 15130.05 |
13 | 15130.05 |
14 | 15130.05 |
15 | 15130.05 |
Now, we can can calculate the interest payment using the function PPMT as below -
PPMT function = PPMT(rate, per, nper, pv, [fv] [type)]
where, rate is the rate of interest, per is the period for which interest is calculated, nper is the number of periods, PV is the value of loan, fv is the remaining balance at end of loan (0 here)
Hence, Principal = Payment - Interest
Loan Amount | 170000 | Loan Amount | 170000 | |||||||
Payment Period | 30 | Payment Period | 15 | |||||||
Interest Rate | 3.85% | Interest Rate | 3.85% | |||||||
Year | Annual Payment | Interest | Principal | Year | Annual Payment | Interest | Principal | Difference | ||
1 | 9652.86 | 6545.00 | 3107.86 | 1 | 15130.05 | 6545.00 | 8585.05 | 5477.20 | ||
2 | 9652.86 | 6425.35 | 3227.51 | 2 | 15130.05 | 6214.48 | 8915.58 | 5688.07 | ||
3 | 9652.86 | 6301.09 | 3351.77 | 3 | 15130.05 | 5871.23 | 9258.83 | 5907.06 | ||
4 | 9652.86 | 6172.05 | 3480.81 | 4 | 15130.05 | 5514.76 | 9615.29 | 6134.48 | ||
5 | 9652.86 | 6038.03 | 3614.82 | 5 | 15130.05 | 5144.57 | 9985.48 | 6370.66 | ||
6 | 9652.86 | 5898.86 | 3753.99 | 6 | 15130.05 | 4760.13 | 10369.92 | 6615.93 | ||
7 | 9652.86 | 5754.34 | 3898.52 | 7 | 15130.05 | 4360.89 | 10769.16 | 6870.64 | ||
8 | 9652.86 | 5604.24 | 4048.61 | 8 | 15130.05 | 3946.28 | 11183.77 | 7135.16 | ||
9 | 9652.86 | 5448.37 | 4204.48 | 9 | 15130.05 | 3515.70 | 11614.35 | 7409.86 | ||
10 | 9652.86 | 5286.50 | 4366.36 | 10 | 15130.05 | 3068.55 | 12061.50 | 7695.14 | ||
11 | 9652.86 | 5118.39 | 4534.46 | 11 | 15130.05 | 2604.18 | 12525.87 | 7991.41 | ||
12 | 9652.86 | 4943.82 | 4709.04 | 12 | 15130.05 | 2121.94 | 13008.12 | 8299.08 | ||
13 | 9652.86 | 4762.52 | 4890.34 | 13 | 15130.05 | 1621.12 | 13508.93 | 8618.59 | ||
14 | 9652.86 | 4574.24 | 5078.61 | 14 | 15130.05 | 1101.03 | 14029.02 | 8950.41 | ||
15 | 9652.86 | 4378.71 | 5274.14 | 15 | 15130.05 | 560.91 | 14569.14 | 9295.00 | ||
16 | 9652.86 | 4175.66 | 5477.20 | |||||||
17 | 9652.86 | 3964.79 | 5688.07 | |||||||
18 | 9652.86 | 3745.80 | 5907.06 | |||||||
19 | 9652.86 | 3518.37 | 6134.48 | |||||||
20 | 9652.86 | 3282.20 | 6370.66 | |||||||
21 | 9652.86 | 3036.93 | 6615.93 | |||||||
22 | 9652.86 | 2782.21 | 6870.64 | |||||||
23 | 9652.86 | 2517.69 | 7135.16 | |||||||
24 | 9652.86 | 2242.99 | 7409.86 | |||||||
25 | 9652.86 | 1957.71 | 7695.14 | |||||||
26 | 9652.86 | 1661.45 | 7991.41 | |||||||
27 | 9652.86 | 1353.78 | 8299.08 | |||||||
28 | 9652.86 | 1034.26 | 8618.59 | |||||||
29 | 9652.86 | 702.45 | 8950.41 | |||||||
30 | 9652.86 | 357.86 | 9295.00 |
The difference is the difference between the principal of both the options.
You are buying a house and trying to decide when you want to mortgage to mature....
You have asked a friend for a loan of $ 50,000. You both agree on an interest rate of 10 %. You promise to pay him/her in installments at the end of every month for next 2 years. Create a model in EXCEL to show a payment schedule by you. Assume today is 1/1/19. You can use PMT function, IPMT function and PPMT function. Each month end should also say how much of the principal is remaining. Use the below...
You are planning to buy a new house. You currently have 535,000 and your bank told you that you would need a 15% down payment Jus an additional 4% in closing costs. If the house that you want to buy costs $250.000 and you can make a 7% annual return on your avestment, determine the following: When will you have enough money for the down payment and closing costs, assuming that the $35.000 is the only investment that you make?...
Total Loan amount: The total mortgage loan amount is the amount you borrow after paying your down payment. Here, we assumed that you would pay 20% of the home value (property value) as a down payment. 2. Months: The mortgage payment period is set to 30 years. In terms of months, this is equivalent to 30 years multiplied by 12 months. We put our primary basis of payments in terms of months, which is why we need to convert everything...
Mortgage Analysis You are planning to purchase a house that costs $480,000. You plan to put 20% down and borrow the remainder. Based on your credit score, you believe that you will pay 3.25% on a 30-year mortgage. (Use Excel) Use function “PMT” to calculate your mortgage payment. Use function “PV” to calculate the loan amount given a payment of $1550 per month. What is the most that you can borrow? Use function “RATE” to calculate the interest rate given...
Mortgage Analysis Part I You are planning to purchase a house that costs $480,000. You plan to put 20% down and borrow the remainder. Based on your credit score, you believe that you will pay 3.99% on a 30-year mortgage. 1. Use function "PMT" to calculate your mortgage payment. 2. Calculate the total cost of the home purchase. (Down payment plus principle (loan amount) plus interest.) 3. Calculate how much interest you will pay in total? 4. Assume that you...
Mortgage Analysis Part I You are planning to purchase a house that costs $480,000. You plan to put 20% down and borrow the remainder. Based on your credit score, you believe that you will pay 3.99% on a 30-year mortgage. Use the function “PMT” to calculate your mortgage payment. Calculate the total cost of the home purchase. (Down payment plus principal (loan amount) plus interest.) Calculate how much interest you will pay in total? Assume that you plan to pay...
Mortgage Information Annual Interest Rate 4.90% Repayment Years 30 Price of House $275,000 Down Payment $55,000 Principal of Loan Monthly Payments On the Mortgage worksheet, use the PMT function in cell B7 to calculate the monthly payments of the mortgage. Use cell locations from this worksheet to define each argument of the function. Assumethat payments are made at the end of each month.On the Mortgage worksheet, use the data provided to enter a formula in cell B6 to calculate the...
This morning, you borrowed $150,000 to buy a house. The mortgage rate is 7 percent. The amortized loan requires you to pay $10,000 in principal plus interest each year over 15 years. The first payment is due one year from today. How much is the second payment?
Lab 2 - Differentials and your Mortgage The monthly payment P on a mortgage loan of A dollars at an APR of r (as a decimal) for t years is given by this formula Р(А, r,t) 3D А -1 2 1-|1+ 12 ӘР ОР дР Determine and state the partial derivatives JA'ar'at uSing a CAS (Computer Algebra 1. System.) Include screen shots of each of these in your report! (15 pts) 2 Find P(250000, 0.035, 15). (10 pts) Use differentials...
Your local lender offers you a fixed-rate mortgage with the following terms: $220,000 at 4.75% for 30 years, monthly payments. The lender will charge you two discount points and the loan has a 3% prepayment penalty. A. (1 pt) What is the annual percentage rate (APR) of the loan? Answer: _______ B. (1 pt) How many points are required to yield an APR of 5.25%? Answer: _______ Suppose you take a fixed-rate mortgage for $200,000 at 5.00% for 30 years,...