Build a spreadsheet that turns any flat rate offer into a monthly payment, a total cost and an EIR.
A car salesperson hands you a financing sheet, or your bank app offers you a pre-approved loan. You have about two minutes to judge the offer before the conversation moves on. You can't realistically work out an EIR by hand in that time, but you can type four numbers into a sheet on your phone.
The calculator works in Excel or Google Sheets. It takes a flat rate offer, with or without a processing fee, and gives you the monthly payment, the total repaid, the cost of borrowing and the EIR. Allow about 25 minutes to build and test it.
The cell positions below are only suggestions. If you use them, the formulas work as written. Labels go in column A and values go in column B, so give each formula row a matching label in column A. The first four rows are the inputs: the amount, the flat rate, the term and the fees. Shade B1 to B4 a colour so you can always see which numbers are meant to be changed. Every cell below them is a formula. Leave row 5 empty.
A1 "Loan amount", B1 10000 A2 "Flat rate per year", B2 3% A3 "Years", B3 5 A4 "Fee taken from loan", B4 0 B6 number of months: =B3*12 B7 total flat interest: =B1*B2*B3 B8 monthly payment: =(B1+B7)/B6 B9 cash actually received: =B1-B4 B10 total repaid: =B8*B6 B11 cost of borrowing: =B10-B9 B12 EIR: =RATE(B6,-B8,B9)*12
Format B12 as a percentage with two decimal places.
The starting values are the test loan from Lesson 3.2, "Why a flat rate loan costs nearly double what it looks like". It is an invented example: S$10,000 at a flat 3% a year over 5 years, with no fee.
B7 works out the interest the way a flat rate is charged: once, on the full loan amount, for the whole term. The sum is loan amount x flat rate x years. B8 adds that flat interest to the loan and splits the total evenly across the months.
B9 is the cash received, which is the loan amount minus any fee taken from the loan. Some lenders add the fee to the loan instead of deducting it. In that case, raise the loan amount in B1 by the fee and also enter the fee in B4. Flat interest is then charged on the larger sum, and B9 still shows the cash you actually receive.
B11 is the cost of borrowing. It covers everything you pay beyond the cash received, interest and fees together.
B12 uses RATE, a spreadsheet function that finds the interest rate per period at which a series of equal payments exactly repays a sum. Here the periods are months (B6), the payment is the monthly instalment (B8) and the sum is the cash received (B9). The payment goes in as a negative number because it is money leaving you. The cash received is positive because it comes to you. This follows the sign convention Microsoft sets out on its PV function page: cash you pay out is a negative number, and cash you receive is a positive number. RATE gives a monthly rate, and multiplying it by 12 turns it into a yearly figure. That yearly figure is the rate on a reducing balance that matches the loan, which is the EIR as this course uses it.
The formula uses the cash received in B9 instead of the loan amount so that the fee is included. When a fee is taken off the top, the same instalments repay less money. RATE then shows a higher rate, and the rate really is higher.
With the test inputs in place, you should see 60 months and S$1,500 of flat interest (10000 x 3% x 5). The monthly payment should be S$191.67 ((10000 + 1500) / 60) and the cash received S$10,000. The total repaid should be S$11,500 (191.67 x 60), the cost of borrowing S$1,500 (11,500 - 10,000) and the EIR about 5.64%. That EIR matches the 5.6% in Lesson 3.2. If any result is different, check the minus sign in RATE first.
Next, set B4 to 500, a S$500 fee in this invented example. The payment stays at S$191.67 and the total repaid stays at S$11,500, because flat interest is still charged on the full loan amount. Cash received drops to S$9,500, the cost of borrowing rises to S$2,000 and the EIR rises to about 7.79%. That figure matches Lesson 3.3, "Fees and charges that change the real cost". Every line should update when you change any input.
Multiplying the monthly rate by 12 is the usual simple convention. If you compound the monthly rate instead, you get a slightly higher figure. For that version, use =(1+RATE(B6,-B8,B9))^12-1. It gives about 5.79% for the test loan with no fee.
Because lenders may use a different convention like this one, their published EIR may differ from your sheet by a fraction of a percentage point. A gap that small is acceptable. A gap of a whole percentage point or more means one of your inputs is different, and often it is a fee you haven't counted.
The finished sheet has four shaded inputs with the calculated lines below them. The EIR cell should show about 5.64% for the S$10,000, 3%, five-year test with no fee, and about 7.79% with the S$500 fee. Save it somewhere you can open it on your phone, because it only helps if you have it with you when an offer appears. From any offer, it needs just four things: the amount, the flat rate, the term and the fees.
Build the EIR calculator and check it against the worked example. Then put two real offers through it and write down which one is cheaper.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).