I have two places that I can’t find the right numbers to fill out my chart to see if I want to take this offer: With amortization on a $230,000 loan at 7% for 5 years, what would the interest in dollars be; what amount of principle would have been paid? With amortization on a $230,0000 loan at 7% for 10 years, what would the interest in dollars be; what amount of principle would have been paid?
Does someone here have a handy dandy excel or other macro that can figure this out easily and let me know?
While you have already gotten a calculator, I would point out that it’s not too difficult to set up an amortization table in Excel. Here’s how I do mine:
Line 1 is the titles for the columns:
Column A: Payment #
Column B: Beginning Principal
Column C: Payment Amount
Column D: Interest Rate
Column E: Interest Paid
Column F: Principal Paid
Column G: Ending Principal
If you would like to see month/year, you can also add in a column for that, or replace the Payment # column with month/year. If you do add a column, be sure to adjust the cell references.
Line 2 shows the first payment for a 10 year (120 month) 7% loan:
Cell A2: 1
Cell B2: $230,000.00
Cell C2: =ROUNDUP(PMT(D2/12,120,-B2),2) - this calculates the monthly payment, where D2 is the annual interest rate, 120 is the number of monthly payments to pay off the loan and B2 is the principal amount
Cell D2: 7%
Cell E2: =ROUND(B2*D2/12,2) - this calculates the part of this payment that is applied to interest
Cell F2: =C2-E2 - this calculates the part of this payment that is applied to principal
Cell G2: =B2-F2 - this calculates the ending balance
Line 3 shows the 2nd payment:
Cell A3: =A2+1
Cell B3: =G2
Cell C3: =C2
Cell D3: 7%
Cell E3: =ROUND(B2*D2/12,2) - this calculates the part of this payment that is applied to interest
Cell F3: =C2-E2 - this calculates the part of this payment that is applied to principal
Cell G3: =B2-F2 - this calculates the ending balance
Then you copy Line 3 for the next 118 lines, to line 121
On line 121, the monthly payment amount will slightly different because of the rounding that has occurred over the course of the loan, so you need to make this change:
Cell C121: =B121+E121
You will now have a complete amortization table for a $230,000 loan for 10 years at 7% If you want to change the term of the loan, you can change the number of months to 60 (or 360), and change the number of lines in the table.
You can determine the total amount of the payments, interest and principal over the course of the loan by summing up columns C, E and F
If your buyer is making payments that are more than the monthly payment amount, you can adjust the table as you go along to correctly calculate the interest and principal for the following months.
AJ