Payment: PMT(rate, nper, pv, fv, type) e.g. PMT(0.04/12, 360, 200000,0,0)
4% interest rate, 30 years (360 payment) and 200000 loan amount.
Interest Payment: IPMT(rate, per, Nper, Pv, Fv), e.g. IPMT(0.04/12, 12, 360, 200000, 0)
12 is the 12th month or the end of first year.
The principle paid that month is PMT - IPMT and the ending balance is:
Total loan amount - (PMT - IPMT), e.g. 200000 - (PMT - IPMT).
You can build a spreadsheet like this:
| Year | Payment Index | Interest | Principle | Payment | Principle | ||
| Interest Rate | 4.000% | 0.08 | 1 | ($666.67) | ($288.16) | ($954.83) | 199,711.84 |
| Term | 360 | 0.17 | 2 | ($665.71) | ($289.12) | ($954.83) | 199,422.71 |
| Loan Amount | $ 200,000 | 0.25 | 3 | ($664.74) | ($290.09) | ($954.83) | 199,132.62 |
| 0.33 | 4 | ($663.78) | ($291.06) | ($954.83) | 198,841.57 | ||
| 5 Year Int. | ($38,185) | 0.42 | 5 | ($662.81) | ($292.03) | ($954.83) | 198,549.54 |
| 10 Year Int. | ($72,148) | 0.50 | 6 | ($661.83) | ($293.00) | ($954.83) | 198,256.54 |
| 15 Year Int. | ($100,955) | 0.58 | 7 | ($660.86) | ($293.98) | ($954.83) | 197,962.57 |
| 20 Year Int. | ($123,468) | 0.67 | 8 | ($659.88) | ($294.96) | ($954.83) | 197,667.61 |
| 30 Year Int. | ($143,739) | 0.75 | 9 | ($658.89) | ($295.94) | ($954.83) | 197,371.67 |
| 0.83 | 10 | ($657.91) | ($296.93) | ($954.83) | 197,074.75 | ||
| 0.92 | 11 | ($656.92) | ($297.91) | ($954.83) | 196,776.83 | ||
| 1.00 | 12 | ($655.92) | ($298.91) | ($954.83) | 196,477.93 |
