Question
In: Finance
You take out a 25-year mortgage for $300,000 to buy a new house. What will your...
You take out a 25-year mortgage for $300,000 to buy a new house. What will your monthly payments be if the interest rate on your mortgage is 8 percent? Now, calculate the portion of the 48th monthly payment that goes toward interest and principal.
Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the green cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section.
| Given Data: | |
| Loan Amount | 300,000 |
| Rate of Interest | 8.00% |
| Number of years | 25 |
| Month # for portion of monthly payment | 48 |
| Months in a year | 12 |
| Data I must complete: | |
| Number of Periods | 300 |
| Monthly Interest Rate | .69% |
| Payment | |
| Interest amount in the 48th payment | |
| Principal amount in the 48th payment |
I filled in the number of periods and Monthly interest rate and believe them to be correct. How am I supposed to get the Payment (D14) amount with knowing the FV? What would be the PMT function?
Solutions
Expert Solution
There are two to do this, use a table in excel or make use of excel formulas of PMT to calculate Equal installments
First we calculate, the installment amount of loan using PMT function.
Here Rate = 8% /12 (As monthly payment, so monthly rate)
NPER = 25 years = 300 Months
PV = Loan Amount at start = $300000
FV =0
Type = 0 (EMI paid at the end of month)
Thus PMT = PMT(8%/12,300,300000,0,0) = - $2315.45 (The negative sign just indicates a cash outflow, in installment payments)
1. Using the FV function - We will calculate the FV (Outstanding loan in the month 47 & 48 to find the principal and interest paid in Month 48)
So we use FV function, where rate = 8%/12, NPER = 47, PMT = -2315.45, PV = 300000, TYpe = 0)
Loan outstanding in month 47 = FV in month 47 = FV(8%/12,47,-2315.45,300000,0) = $282,655.50
Loan outstanding in month 48 = FV in month 48 = FV(8%/12,48,-2315.45,300000,0) = $282,224.42
Outstanding loan has come down by (282655.50 - 282224.42) = $431.08
This $431.08 is the principal component of the Installment of $2315.45 (The amount paid every month, a part goes in principal payment and other in Interest payment)
Interest component of 48th installment = $2315.45 - $431.08 = $1884.37
Principal component of 48th installment = $431.08
2. Alternatively, Using the excel table
One can see that loan outstanding at end =0,
Principal paid in 48th installment = 431.08 & Interest payment = 1884.37
You can replicate the table in excel (by creating a new sheet and using the below mentioned formulas - 1. Screen shot , 2. Formula table, 3. The output of data
| Loan amount | 300000 | ||||
| Rate of Interest | =8%/12 | Monthly rate | |||
| Period of loan | 300 | (25 years = 300 months) | |||
| FV | 0 | ||||
| Type | 0 | ||||
| PMT | =PMT(B2,300,300000,0,0) | ||||
| Month | Opening Balance (A) | Interest B = A x 8%/12 | EMI = 2315.45 = C | Principal (D) = C - B |
Closing balance (E = A + B - C) |
| 1 | =300000 | =B8*8%/12 | =-B6 | =D8-C8 | =B8+C8-D8 |
| =A8+1 | =F8 | =B9*8%/12 | =D8 | =D9-C9 | =B9+C9-D9 |
| =A9+1 | =F9 | =B10*8%/12 | =D9 | =D10-C10 | =B10+C10-D10 |
| =A10+1 | =F10 | =B11*8%/12 | =D10 | =D11-C11 | =B11+C11-D11 |
| =A11+1 | =F11 | =B12*8%/12 | =D11 | =D12-C12 | =B12+C12-D12 |
| =A12+1 | =F12 | =B13*8%/12 | =D12 | =D13-C13 | =B13+C13-D13 |
| =A13+1 | =F13 | =B14*8%/12 | =D13 | =D14-C14 | =B14+C14-D14 |
| =A14+1 | =F14 | =B15*8%/12 | =D14 | =D15-C15 | =B15+C15-D15 |
| =A15+1 | =F15 | =B16*8%/12 | =D15 | =D16-C16 | =B16+C16-D16 |
| =A16+1 | =F16 | =B17*8%/12 | =D16 | =D17-C17 | =B17+C17-D17 |
| =A17+1 | =F17 | =B18*8%/12 | =D17 | =D18-C18 | =B18+C18-D18 |
| Loan amount | 300000 | ||||
| Rate of Interest | 0.67% | Monthly rate | |||
| Period of loan | 300 | (25 years = 300 months) | |||
| FV | 0 | ||||
| Type | 0 | ||||
| PMT | ($2,315.45) | ||||
| Month | Opening Balance (A) | Interest B = A x 8%/12 | EMI = 2315.45 = C |
Principal (D) = C - B |
Closing balance (E = A + B - C) |
| 1 | $300,000.00 | $2,000.00 | $2,315.45 | $315.45 | $299,684.55 |
| 2 | $299,684.55 | $1,997.90 | $2,315.45 | $317.55 | $299,367.00 |
| 3 | $299,367.00 | $1,995.78 | $2,315.45 | $319.67 | $299,047.33 |
| 4 | $299,047.33 | $1,993.65 | $2,315.45 | $321.80 | $298,725.53 |
| 5 | $298,725.53 | $1,991.50 | $2,315.45 | $323.95 | $298,401.59 |
| 6 | $298,401.59 | $1,989.34 | $2,315.45 | $326.10 | $298,075.48 |
| 7 | $298,075.48 | $1,987.17 | $2,315.45 | $328.28 | $297,747.20 |
| 8 | $297,747.20 | $1,984.98 | $2,315.45 | $330.47 | $297,416.74 |
| 9 | $297,416.74 | $1,982.78 | $2,315.45 | $332.67 | $297,084.06 |
| 10 | $297,084.06 | $1,980.56 | $2,315.45 | $334.89 | $296,749.18 |
| 11 | $296,749.18 | $1,978.33 | $2,315.45 | $337.12 | $296,412.06 |
| 12 | $296,412.06 | $1,976.08 | $2,315.45 | $339.37 | $296,072.69 |
| 13 | $296,072.69 | $1,973.82 | $2,315.45 | $341.63 | $295,731.06 |
| 14 | $295,731.06 | $1,971.54 | $2,315.45 | $343.91 | $295,387.15 |
| 15 | $295,387.15 | $1,969.25 | $2,315.45 | $346.20 | $295,040.95 |
| 16 | $295,040.95 | $1,966.94 | $2,315.45 | $348.51 | $294,692.44 |
| 17 | $294,692.44 | $1,964.62 | $2,315.45 | $350.83 | $294,341.61 |
| 18 | $294,341.61 | $1,962.28 | $2,315.45 | $353.17 | $293,988.43 |
| 19 | $293,988.43 | $1,959.92 | $2,315.45 | $355.53 | $293,632.91 |
| 20 | $293,632.91 | $1,957.55 | $2,315.45 | $357.90 | $293,275.01 |
| 21 | $293,275.01 | $1,955.17 | $2,315.45 | $360.28 | $292,914.73 |
| 22 | $292,914.73 | $1,952.76 | $2,315.45 | $362.68 | $292,552.05 |
| 23 | $292,552.05 | $1,950.35 | $2,315.45 | $365.10 | $292,186.95 |
| 24 | $292,186.95 | $1,947.91 | $2,315.45 | $367.54 | $291,819.41 |
| 25 | $291,819.41 | $1,945.46 | $2,315.45 | $369.99 | $291,449.42 |
| 26 | $291,449.42 | $1,943.00 | $2,315.45 | $372.45 | $291,076.97 |
| 27 | $291,076.97 | $1,940.51 | $2,315.45 | $374.94 | $290,702.04 |
| 28 | $290,702.04 | $1,938.01 | $2,315.45 | $377.44 | $290,324.60 |
| 29 | $290,324.60 | $1,935.50 | $2,315.45 | $379.95 | $289,944.65 |
| 30 | $289,944.65 | $1,932.96 | $2,315.45 | $382.48 | $289,562.17 |
| 31 | $289,562.17 | $1,930.41 | $2,315.45 | $385.03 | $289,177.13 |
| 32 | $289,177.13 | $1,927.85 | $2,315.45 | $387.60 | $288,789.53 |
| 33 | $288,789.53 | $1,925.26 | $2,315.45 | $390.19 | $288,399.34 |
| 34 | $288,399.34 | $1,922.66 | $2,315.45 | $392.79 | $288,006.56 |
| 35 | $288,006.56 | $1,920.04 | $2,315.45 | $395.40 | $287,611.15 |
| 36 | $287,611.15 | $1,917.41 | $2,315.45 | $398.04 | $287,213.11 |
| 37 | $287,213.11 |
 jeff jeffy answered 1 year agoRelated Solutions(Components of an annuity payment) You take out a 25-year mortgage for 300,000 to buy a...(Components of an annuity payment) You take out a
25-year
mortgage for
300,000
to buy a new house. What will your monthly payments be if the
interest rate on your mortgage is
88
percent? Use a spreadsheet to calculate your answer. Now,
calculate the portion of the
48th
monthly payment that goes toward interest and principal. Use
five decimal places for the monthly interest rate in your
calculations.
a. Using a spreadsheet to calculate your answer, your monthly
payments will...
(Components of an annuity payment) You take out a 25-year mortgage for 300,000 to buy a...(Components of an annuity payment) You take out a
25-year
mortgage for
300,000
to buy a new house. What will your monthly payments be if the
interest rate on your mortgage is
88
percent? Use a spreadsheet to calculate your answer. Now,
calculate the portion of the
48th
monthly payment that goes toward interest and principal. Use
five decimal places for the monthly interest rate in your
calculations.
a. Using a spreadsheet to calculate your answer, your monthly
payments will...
You take out a mortgage to buy a house worth $300,000. If the down payment is...You take out a mortgage to buy a house worth $300,000. If the
down payment is 30%, the annual interest rate is 6% compounded
monthly, and the term of the mortgage is 30 years, what are your
monthly mortgage payments?
a) $1,079
b) $1,259
c) $1,439
d) $1,505
e) $1,719
Assume you want to take out a $300,000 loan on a 25-year mortgage with end of...Assume you want to take out a $300,000 loan on a 25-year
mortgage with end of month payments. The annual rate of interest is
8 percent. 20 years from now, you need to make an ending additional
lump sum payment of $45,000. Because you expect your income to
increase, you want to structure the loan so that the beginning of
each year your monthly payments increase by 2.5 percent. Determine
the amount of each year’s monthly payment with Excel Solver.
You take out a 30-year mortgage to buy a house worth $449,000. The down payment is...You take out a 30-year mortgage to buy a house worth $449,000.
The down payment is 7% and the annual interest rate is 4.9%. What
are the monthly payments? Round to the nearest cent.
You take out a 30-year mortgage to buy a house worth $312,000. The down payment is...You take out a 30-year mortgage to buy a house worth $312,000.
The down payment is 21% and the annual interest rate is 4.4%. What
are the monthly payments? Round to the nearest cent.
You take out a 30-year mortgage to buy a house worth $396,000. The down payment is...You take out a 30-year mortgage to buy a house worth $396,000.
The down payment is 19% and the annual interest rate is 4.9%. What
are the monthly payments? Round to the nearest cent.
You buy a house at $255000. You pay $25000 down and you take out a mortgage...You buy a house at $255000. You pay $25000 down and you take out
a mortgage at 4.45% compounded monthly on the balance for 30 years.
1. find monthly payment. 2.find total amount of interest you will
pay for 30 years 3. created authorization table with payment
number, monthly payment, interest per month, portion to principal,
principal at the end of the year.
8. You need a 25-year, fixed-rate mortgage to buy a new home for $240,000. Your mortgage...8. You need a 25-year, fixed-rate
mortgage to buy a new home for $240,000. Your mortgage bank will
lend you the money at a 7.5 percent APR for this 300-month loan,
with interest compounded monthly. However, you can only afford
monthly payments of $850, so you offer to pay off any remaining
loan balance at the end of the loan in the form of a single balloon
payment. What will be the amount of the balloon payment if you are...
You need a 25-year, fixed-rate mortgage to buy a new home for $210,000. Your mortgage bank...You need a 25-year, fixed-rate mortgage to buy a new home for
$210,000. Your mortgage bank will lend you the money at a 7.6
percent APR for this 300-month loan. However, you can afford
monthly payments of only $850, so you offer to pay off any
remaining loan balance at the end of the loan in the form of a
single balloon payment.
How large will this balloon payment have to be for you to keep
your monthly payments at...
ADVERTISEMENT
ADVERTISEMENT
Latest Questions
ADVERTISEMENT
|