How to determine effective interest rate?

[dtighe]

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:

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

<tbody>
</tbody>

Thanks

Derek

 

[Trevor G]

Welcome to the Forum Derek have you explored the PMT (Payment Monthly Terms) formula and there are variables on this formula.

Excel PMT() Function Basics - YouTube

 

[dtighe]

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

Period	Months	Date	Balance B/f	Repayments
0		01/10/2012	100,000.00
1	3			-5,962.50
2	6			-5,914.38
3	9			-5,866.25
4	12			-5,818.13
5	15			-5,770.00
6	18			-5,721.88
7	21			-5,673.75
8	24			-5,625.63
9	27			-5,577.50
10	30			-5,529.38
11	33			-5,481.25
12	36			-5,433.13
13	39			-5,385.00
14	42			-5,336.88
15	45			-5,288.75
16	48			-5,240.63
17	51			-5,192.50
18	54			-5,144.38
19	57			-5,096.25
20	60			-5,048.13
		Total		-110,106.25

<tbody>
</tbody><colgroup><col><col><col><col><col></colgroup>

 

[MickG]

Have you tried the "XIRR" function.
Possible result 3.9%

 

[dtighe]

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

 

[MickG]

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)=

1.009627115

<COLGROUP><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4010" width=113><TBODY>
</TBODY>

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 ????

1.0390954	1.009627115
100000
95000	962.50
90000	914.38
85000	866.25
80000	818.13
75000	770.00
70000	721.88
65000	673.75
60000	625.63
55000	577.50
50000	529.38
45000	481.25
40000	433.13
35000	385.00
30000	336.88
25000	288.75
20000	240.63
15000	192.50
10000	144.38
5000	96.25
0	48.13

<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>
</TBODY>

 

[dtighe]

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

 

[MickG]

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)

Compound Mthly rate Mthly rate Comp 1Yr Comp 1Yr £100 1.00797414 =1.1^(1/12) 110 =100*1.00797414^12 Fraction Mthly rate Mthly rate Comp 1Yr Comp 1Yr £100 0.008333333 =0.1/12 110.4713067 =100*(1+0.0083333)^12 <TBODY> </TBODY>

<TBODY>
</TBODY>

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

Regrds Mick

<TBODY>
</TBODY>

 

[dtighe]

Thanks Mick