You will build a spreadsheet that turns any flat rate offer into a monthly payment, a total cost and an EIR.
The next time a car salesperson slides a financing sheet across the desk, or a bank app offers you a pre-approved loan, you will have about two minutes to judge it before the conversation moves on. Working out an EIR by hand in that time is not realistic. Typing four numbers into a sheet on your phone is.
In this exercise you build that sheet in Excel or Google Sheets. It takes a flat rate offer, with or without a processing fee, and returns the monthly payment, the total you repay, the cost of borrowing and the EIR. Allow about 25 minutes. The cell positions below are a suggestion; if you use them, the formulas work as written.
In column A, type labels, and in column B, the values from the offer. For now, use the example loan from lesson 3.2, Why a flat rate loan costs nearly double what it looks like.
A1 "Loan amount", B1 10000 A2 "Flat rate per year", B2 3% A3 "Years", B3 5 A4 "Fee taken from loan", B4 0
Shade these four cells a colour so you always know which numbers you are meant to change. Everything below them is a formula.
Leave row 5 empty. Then, with labels in column A:
In B6, the number of months, type =B3*12.
In B7, the total flat interest, type =B1*B2*B3. This is the loan amount times the flat rate times the years, worked out once on the full sum, which is how a flat rate is charged.
In B8, the monthly payment, type =(B1+B7)/B6. You repay the loan plus all the flat interest, split evenly across the months.
In B9, the cash you actually receive, type =B1-B4. If the lender adds the fee to your loan rather than deducting it, raise the loan amount in B1 by the fee and enter the fee in B4 as well. Flat interest is then charged on the larger sum, while B9 still shows the cash you get.
In B10, the total repaid, type =B8*B6. In B11, the cost of borrowing, type =B10-B9. This is everything you pay beyond the cash you got, interest and fees together.
In B12 type =RATE(B6,-B8,B9)*12 and format it as a percentage with two decimal places.
RATE finds the interest rate per period that makes a series of equal payments exactly repay a sum. Here the periods are months, the payment is your monthly instalment, and the sum is the cash you received. The payment goes in as a negative number because it is money leaving you, while the cash received is positive because it comes to you. If the signs are the same, RATE returns an error.
RATE gives a monthly rate, so multiplying by 12 turns it into a yearly figure. That yearly figure is the rate on a reducing balance that matches your loan, which is the EIR as this course uses it.
Using the cash you receive in B9, rather than the loan amount, is what captures the fee. A fee taken off the top means you repay the same instalments on less money, and RATE sees that as a higher rate, which it is.
With the inputs above, your sheet should show 60 months, flat interest of S$1,500, a monthly payment of S$191.67, cash received of S$10,000, a total repaid of S$11,500, a cost of borrowing of S$1,500, and an EIR of about 5.64%. That is the 5.6% from lesson 3.2. If you get something else, check the minus sign in RATE first.
Now test the fee. Change B4 to 500. The payment and total repaid stay the same, because the flat interest is still charged on S$10,000. Cash received drops to S$9,500, the cost of borrowing rises to S$2,000, and the EIR rises to about 7.79%. This matches lesson 3.3, Fees and charges that change the real cost.
One note on method. Multiplying the monthly rate by 12 is the usual simple convention. If you compound it instead, with =(1+RATE(B6,-B8,B9))^12-1, the no-fee example comes to about 5.79%. A lender's published EIR may differ from your sheet by a fraction of a percentage point for reasons like this. A gap of a whole point or more means an input is different, often a fee you have not counted.
A finished calculator has four shaded inputs, the derived lines below them, and an EIR cell that shows about 5.64% for the S$10,000, 3%, five-year test with no fee and about 7.79% with a S$500 fee. Change any input and every line updates.
Save it somewhere you can open on your phone, since the sheet only helps if it is 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, test it on the worked example, then run two real offers through it and write which is cheaper.
Junxiong-WFG Organisation is an authorised representative of AIA Financial Advisers Private Limited (Reg. No. 201715016G).