This post provides instruction for you to create a Mortgage Excel sheet where you can work out payments including interest and principal payments of a home loan (table loan type), based on PMT, IPMT and PPMT functions.
Create the Mortgage Excel sheet
Name the sheet “Mortgage” and enter values as below:
| Cell | Value | Cell | Value |
|---|---|---|---|
| A1 | Type | A2 | Fixed 12 months |
| B1 | Loan Amount | B2 | $100,000 |
| C1 | Term (years) | C2 | 10 |
| D1 | Repayment Frequency | D2 | Fortnightly |
| E1 | Number of Payments Per Year | E2 | 26 |
| F1 | Interest Rate | F2 | 5.00% |
| G1 | Fortnight Payment | G2 | =-PMT(F2/E2, C2*E2,B2) |
To see the details of how much interest, principal and the balance on each fortnight, you can create a detail table as below:
| Cell | Value | Cell | Value |
|---|---|---|---|
| A12 | Fortnight | A13 | 1 |
| B12 | Payment | B13 | =ROUND($G$2,2) |
| C12 | Interest | C13 | =-IPMT($F$2/$E$2,A13,$C$2*$E$2,$B$2) |
| D12 | Principal | D13 | =-PPMT($F$2/$E$2,A13,$C$2*$E$2,$B$2) |
| E12 | Balance | E13 | =B2-D13 |
| A14 | 2 | E14 | =E13-D14 |
Next, select the range A13:A14 and drag down to cell A272 (Tip: move the mouse cursor to the right bottom corner of cell A14 until the mouse cursor turns to a + sign then drag down). Cell A272 now should have the value 260.
After that, select range B13:D13 and copy down the formula until row 272 ((Tip: move the mouse cursor to the right bottom corner of cell D13 until the mouse cursor turns to a + sign then double click)
Finally, select cell E14, move the mouse cursor to the right bottom corner of the cell until the mouse cursor turns to a + sign then double click to copy down the formula. Cell E272 should have the value of 0.00.
Congratulations, now you have a mortgage Excel sheet to work out and keep track of payments, principals, interests and balances.
Your sheet should look like the screenshot below:
Notes: This post is not a financial advisory post. It only explains how to find out payments, principals, interests and balances. Values chosen here are examples only. It is totally up to you making decision on loan type, how much loan, frequency of payment, term etc. and other things such as paying a lump sum etc. Therefore, no claim should be made against this post should you experience any financial outcomes.