- Financial calculator
- Spreadsheet, like Excel
Excel functions
First of all, we will look at the three functions we need to use (Do note that most other spreadsheet programs use the exact same functions). 1. PMT (Rate, NPer,PV, FV, Type): Total payment (principle and interest) payable for that compounding period 2. PPMT (Rate, Per, NPer, PV, FV, Type): Principle payable for that compounding period 3. IPMT (Rate, Per, NPer, PV, FV, Type): Interest payable for that compounding period So what is each of the variables in the parenthesis? Note that the ones in italics are optional.- Rate (Required): Nominal interest rate for that compounding period
- NPer (Required): Total number of compounding periods
- PV (Required): Present value of the loan
- FV: Loan amount outstanding after all payments have been made. If this variable is omitted, Excel will assume the default value of 0.
- Type: The timing of the payment. It can be either 0 or1. If this variable is omitted, Excel will assume the default value of 0.
Value |
Explanation |
0 | Payments are due at the end of the period. (default) |
1 | Payments are due at the beginning of the period. |
- Per: The particular compounding period for which you want to find the interest or principle payable.
Creating the amortisation schedule
Rest, or the compounding period, is the frequency in which the outstanding loan amount is calculated. For the purpose of this exercise, we will assume the most common case of a monthly-rest loan. Example: Rest = Monthly Loan Amount = $ 1,000,000 Loan Duration = 30 years Interest Rate = 3% per annum for the first year 5% per annum thereafter We enter these information into the Excel sheet, as below As the frequency in which the outstanding loan is calculated once a month, the number of compounding period each year is 12. The loan lasts for 30 years; hence the 'Total Periods' is 12 x 30 = 360 Since compounding is done 12 times a year, the 'Nominal Monthly Interest Rate' becomes 3% / 12 = 0.0025. Next, we proceed to set up the amortisation table as below We will need to make use of the functions to obtain the 'Payment', 'Principle', 'Interest' and 'Balance'. Table 1 illustrates the syntax to enter for each of the cells. Note that we include $ for some variables, this is to freeze the cell references so that they remain the same as we drag the formulas down.Table 1
Cell |
Enter |
G12 |
=B2 |
D13 |
=PMT(B$6,B$4,-B$2) |
E13 |
=PPMT(B$6,C13,B$4,-B$2) |
F13 |
=IPMT(B$6,C13,B$4,-B$2) |
G13 |
=G12-E13 |
About Property Buyer http://www.PropertyBuyer.com.sg/mortgage We are a research-focused Singapore mortgage consultancy which helps you compare Singapore home loans either for new loans or refinancing. We use loan reports from Singapore's best loan analysis system (exclusive to us) at http://www.icompareloan.com/consultant/ to serve our customers. Our services are completely FREE to you as the banks pay us a referral fee upon loan disbursement. SMS: (65) 9782 8606 Email: loans@PropertyBuyer.com.sg