How to determine effective interest rate?

D

dtighe

New Member
Joined
Oct 12, 2012
Messages
5
Hi

I have a series of varying future cashflows and I have the initial loan amount, how do I calculate the effective interest rate in excel?

Example

Loan amount €100,000

Term: 60 months

Repayments quarterly uneven:

[TABLE="width: 572"]
<tbody>[TR]
[TD]Months
[/TD]
[TD]Date
[/TD]
[TD]Balance B/f
[/TD]
[TD]Interest
[/TD]
[TD] Repayments
[/TD]
[TD]Balance C/f
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/10/2012
[/TD]
[TD] 100,000.00
[/TD]
[TD][/TD]
[TD][/TD]
[TD] 100,000.00
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]31/12/2012
[/TD]
[TD] 100,000.00
[/TD]
[TD] 962.50
[/TD]
[TD] (5,962.50)
[/TD]
[TD] 95,000.00
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]31/03/2013
[/TD]
[TD] 95,000.00
[/TD]
[TD] 914.38
[/TD]
[TD] (5,914.38)
[/TD]
[TD] 90,000.00
[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]30/06/2013
[/TD]
[TD] 90,000.00
[/TD]
[TD] 866.25
[/TD]
[TD] (5,866.25)
[/TD]
[TD] 85,000.00
[/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]30/09/2013
[/TD]
[TD] 85,000.00
[/TD]
[TD] 818.13
[/TD]
[TD] (5,818.13)
[/TD]
[TD] 80,000.00
[/TD]
[/TR]
[TR]
[TD]15
[/TD]
[TD]31/12/2013
[/TD]
[TD] 80,000.00
[/TD]
[TD] 770.00
[/TD]
[TD] (5,770.00)
[/TD]
[TD] 75,000.00
[/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]31/03/2014
[/TD]
[TD] 75,000.00
[/TD]
[TD] 721.88
[/TD]
[TD] (5,721.88)
[/TD]
[TD] 70,000.00
[/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]30/06/2014
[/TD]
[TD] 70,000.00
[/TD]
[TD] 673.75
[/TD]
[TD] (5,673.75)
[/TD]
[TD] 65,000.00
[/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD]30/09/2014
[/TD]
[TD] 65,000.00
[/TD]
[TD] 625.63
[/TD]
[TD] (5,625.63)
[/TD]
[TD] 60,000.00
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]31/12/2014
[/TD]
[TD] 60,000.00
[/TD]
[TD] 577.50
[/TD]
[TD] (5,577.50)
[/TD]
[TD] 55,000.00
[/TD]
[/TR]
[TR]
[TD]30
[/TD]
[TD]31/03/2015
[/TD]
[TD] 55,000.00
[/TD]
[TD] 529.38
[/TD]
[TD] (5,529.38)
[/TD]
[TD] 50,000.00
[/TD]
[/TR]
[TR]
[TD]33
[/TD]
[TD]30/06/2015
[/TD]
[TD] 50,000.00
[/TD]
[TD] 481.25
[/TD]
[TD] (5,481.25)
[/TD]
[TD] 45,000.00
[/TD]
[/TR]
[TR]
[TD]36
[/TD]
[TD]30/09/2015
[/TD]
[TD] 45,000.00
[/TD]
[TD] 433.13
[/TD]
[TD] (5,433.13)
[/TD]
[TD] 40,000.00
[/TD]
[/TR]
[TR]
[TD]39
[/TD]
[TD]31/12/2015
[/TD]
[TD] 40,000.00
[/TD]
[TD] 385.00
[/TD]
[TD] (5,385.00)
[/TD]
[TD] 35,000.00
[/TD]
[/TR]
[TR]
[TD]42
[/TD]
[TD]31/03/2016
[/TD]
[TD] 35,000.00
[/TD]
[TD] 336.88
[/TD]
[TD] (5,336.88)
[/TD]
[TD] 30,000.00
[/TD]
[/TR]
[TR]
[TD]45
[/TD]
[TD]30/06/2016
[/TD]
[TD] 30,000.00
[/TD]
[TD] 288.75
[/TD]
[TD] (5,288.75)
[/TD]
[TD] 25,000.00
[/TD]
[/TR]
[TR]
[TD]48
[/TD]
[TD]30/09/2016
[/TD]
[TD] 25,000.00
[/TD]
[TD] 240.63
[/TD]
[TD] (5,240.63)
[/TD]
[TD] 20,000.00
[/TD]
[/TR]
[TR]
[TD]51
[/TD]
[TD]31/12/2016
[/TD]
[TD] 20,000.00
[/TD]
[TD] 192.50
[/TD]
[TD] (5,192.50)
[/TD]
[TD] 15,000.00
[/TD]
[/TR]
[TR]
[TD]54
[/TD]
[TD]31/03/2017
[/TD]
[TD] 15,000.00
[/TD]
[TD] 144.38
[/TD]
[TD] (5,144.38)
[/TD]
[TD] 10,000.00
[/TD]
[/TR]
[TR]
[TD]57
[/TD]
[TD]30/06/2017
[/TD]
[TD] 10,000.00
[/TD]
[TD] 96.25
[/TD]
[TD] (5,096.25)
[/TD]
[TD] 5,000.00
[/TD]
[/TR]
[TR]
[TD]60
[/TD]
[TD]30/09/2017
[/TD]
[TD] 5,000.00
[/TD]
[TD] 48.13
[/TD]
[TD] (5,048.13)
[/TD]
[TD] -
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total
[/TD]
[TD][/TD]
[TD] 10,106.25
[/TD]
[TD] (110,106.25)
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]



Thanks

Derek
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Thanks Trevor, I belive that I have not asked the question above correctly. The interest element is unknown and should be unknown in my example above. So below I rephrase the question where the interest rate and quatum are unkown?

Example

Loan amount €100,000

Term: 60 months / Quarterly repayments

Interest rate / amount : unknown

Repayments: uneven
[TABLE="width: 411"]
<tbody>[TR]
[TD]Period[/TD]
[TD]Months[/TD]
[TD]Date[/TD]
[TD]Balance B/f[/TD]
[TD]Repayments [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD] [/TD]
[TD]01/10/2012[/TD]
[TD]100,000.00[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]3[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,962.50[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]6[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,914.38[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]9[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,866.25[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,818.13[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,770.00[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,721.88[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]21[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,673.75[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]24[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,625.63[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]27[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,577.50[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]30[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,529.38[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]33[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,481.25[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]36[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,433.13[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]39[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,385.00[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]42[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,336.88[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]45[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,288.75[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]48[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,240.63[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]51[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,192.50[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]54[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,144.38[/TD]
[/TR]
[TR]
[TD]19[/TD]
[TD]57[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,096.25[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]60[/TD]
[TD] [/TD]
[TD] [/TD]
[TD]-5,048.13[/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD][/TD]
[TD] [/TD]
[TD]Total[/TD]
[TD] [/TD]
[TD]-110,106.25[/TD]
[/TR]
</tbody><colgroup><col><col><col><col><col></colgroup>[/TABLE]
 
Upvote 0
Thanks Mick

That formula approximates the interest rate but it is not entirely accurate as it is returning me a value of 3.90952% whereas the actual interest rate in the example was 3.85%. Unless some of my inputs are incorrect but I think I have the various boxes completed correctly. Is there a function that calculates the interest to the exact %?

Thanks

Derek
 
Upvote 0
It seems that the "XIRR" rate of 1.0390954 represents the Yearly rate.
If you Factorise for a Quarterly rate (basically in line with your dates) as:-
=1.0390954^(91.25/365.25)=[TABLE="width: 85"]
<COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4010" width=113><TBODY>[TR]
[TD="class: xl24, width: 113, bgcolor: transparent"]1.009627115[/TD]
[/TR]
</TBODY>[/TABLE]
If you then use that Rate to multiply by 100,000 while reducing the balance by the interest rate +5000, you will get exactly the same data that you already have Like below
Initially this would appear to vindicate the Returned rate ????

[TABLE="width: 138"]
<COLGROUP><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2730" width=77><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3811" width=107><TBODY>[TR]
[TD="class: xl24, width: 77, bgcolor: transparent, align: right"]1.0390954[/TD]
[TD="class: xl25, width: 107, bgcolor: transparent"]1.009627115[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]100000[/TD]
[TD="class: xl26, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]95000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]962.50[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]90000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]914.38[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]85000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]866.25[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]80000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]818.13[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]75000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]770.00[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]70000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]721.88[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]65000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]673.75[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]60000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]625.63[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]55000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]577.50[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]50000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]529.38[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]45000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]481.25[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]40000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]433.13[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]35000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]385.00[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]30000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]336.88[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]25000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]288.75[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]20000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]240.63[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]15000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]192.50[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]10000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]144.38[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]5000[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]96.25[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent, align: right"]0[/TD]
[TD="class: xl26, bgcolor: transparent, align: right"]48.13[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
I agree with your proof above and this works. But I am still a little puzzled why the amortisation table in my first post where the rate is 3.850% per annum gives the same amortisation profile as using the XIRR rate of 3.90952%? Take for example how the first quarter method is calculated

Using my method of calculation and interest rate of 3.85% gives : €100,000 * 3.85% * (91.25/365.25)= €961.84 interest
Using the factor method of calculation and interest rater of 3.90952% gives : €100,000 * 0.009627066 = €962.71interest
(Note factor is calculated as 1.0390952^(91.25/365.25) - 1 = 0.009627066)

Noted there is a small difference in the interest amount which is immaterial for me but it is the larger difference in the headline interest rate that I wish to explain why one is 0.05% (3.85% versus 3.90%) greater than the other yet calculated in different ways yields the same result.

Thanks Derek
 
Upvote 0
If you Take you initial rate of 0.0385 & divide by 4 you get = 0.009625 which is almost identical to 1.0390952^(91.25/365.25) - 1 = 0.009627066 and gives the correct results in your data.
I think the Result is dependent on the method used.
If you look at the PMT function you see that Years are divided by 12 as opposed to the 12 root, which I don't understand.
I think the compounding method gives the more logicsal result!!.
Below is a simple example:-

Invest £100 for a Year @ 10% (Method 1, uses 12th root of 1.1 and Method 2, uses 0.1/12)
[TABLE="width: 79"]
<TBODY>[TR]
[TD="width: 106, bgcolor: transparent"][TABLE="width: 477"]
<TBODY>[TR]
[TD="class: xl25, width: 106, bgcolor: #ccffcc"]Compound
[/TD]
[TD="class: xl25, width: 142, bgcolor: #ccffcc"]Mthly rate
[/TD]
[TD="class: xl25, width: 130, bgcolor: #ccffcc"]Mthly rate
[/TD]
[TD="class: xl25, width: 96, bgcolor: #ccffcc"]Comp 1Yr
[/TD]
[TD="class: xl25, width: 164, bgcolor: #ccffcc"]Comp 1Yr
[/TD]
[/TR]
[TR]
[TD="class: xl26, bgcolor: #ccffcc, align: right"]£100
[/TD]
[TD="class: xl25, bgcolor: #ccffcc, align: right"]1.00797414
[/TD]
[TD="class: xl25, bgcolor: #ccffcc"]=1.1^(1/12)
[/TD]
[TD="class: xl25, bgcolor: #ccffcc, align: right"]110
[/TD]
[TD="class: xl25, bgcolor: #ccffcc"]=100*1.00797414^12
[/TD]
[/TR]
[TR]
[TD="class: xl24, bgcolor: transparent"][/TD]
[TD="class: xl24, bgcolor: transparent"][/TD]
[TD="class: xl24, bgcolor: transparent"][/TD]
[TD="class: xl24, bgcolor: transparent"][/TD]
[TD="class: xl24, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl27, bgcolor: #ccffff"]Fraction
[/TD]
[TD="class: xl27, bgcolor: #ccffff"]Mthly rate
[/TD]
[TD="class: xl27, bgcolor: #ccffff"]Mthly rate
[/TD]
[TD="class: xl27, bgcolor: #ccffff"]Comp 1Yr
[/TD]
[TD="class: xl27, bgcolor: #ccffff"]Comp 1Yr
[/TD]
[/TR]
[TR]
[TD="class: xl28, bgcolor: #ccffff, align: right"]£100
[/TD]
[TD="class: xl27, bgcolor: #ccffff, align: right"]0.008333333
[/TD]
[TD="class: xl27, bgcolor: #ccffff"]=0.1/12
[/TD]
[TD="class: xl27, bgcolor: #ccffff, align: right"]110.4713067
[/TD]
[TD="class: xl27, bgcolor: #ccffff"]=100*(1+0.0083333)^12
[/TD]
[/TR]
</TBODY>[/TABLE]

[/TD]
[/TR]
</TBODY>[/TABLE]

This Data does not seem to have posted too well , I should paste it on a sheet.

Regrds Mick

[TABLE="width: 67"]
<TBODY>[TR]
[TD="width: 90, bgcolor: transparent, align: right"][/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

L
  • Solved
Help Please
2 3
Replies
21
Views
405
Asbestos_Jen
Asbestos_Jen
S
  • Question Question
Need Help in Dates
2 3
Replies
27
Views
1K
Dave Patton
D
F
  • Question Question
Graph Help
Replies
2
Views
296
Fegal
F
A
  • Question Question
Create report by matching part of item and populate for each cell under item across sheets
Replies
2
Views
203
Ali M
A
P
  • Solved
Computing investment return formula
Replies
8
Views
662
polarwoc
P

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top