Unequal Lease Payments - Calc Implicit Interest Rate

[gward31]

Hi there

I wonder if you could guide me on the formula to use to calculate the implicit interest rate for a loan with unequal lease payments.

Lease was entered into in Jan 2000 with payments made annually in arrears
PV = 103 630
FV = 15 545
Payments
01 Jan 2001 = 2 000
01 Jan 2002 = 2 000
01 Jan 2003 = 2 000
01 Jan 2004 = 2 000
01 Jan 2005 = 2 000
01 Jan 2006 = 29 564
01 Jan 2001 = 35 477
01 Jan 2001 = 42 571
01 Jan 2001 = 51 086
01 Jan 2001 = 61 303

Thank you very much for the assistance.

Glenda

 

[Richard Schollar]

Hi Glenda & Welcome to the Board!

The implicit rate of interest is effectively the Internal Rate of Return on the payments/receipts. Now, assuming your last 4 dates were incorrect (you have them all as 2001) and they were actually subsequent years and at 1 Jan 2010 you still have 15,545 of capital outstanding (ie not repaid) you can calculate the IRR using the XIRR function (for which I think you need the Analysis Toolpak installed if using xl2003 or belwo):

Excel Workbook
	A	B
1	01-Jan-00	103630
2	01-Jan-01	-2000
3	01-Jan-02	-2000
4	01-Jan-03	-2000
5	01-Jan-04	-2000
6	01-Jan-05	-2000
7	01-Jan-06	-29564
8	01-Jan-07	-35477
9	01-Jan-08	-42571
10	01-Jan-09	-51086
11	01-Jan-10	-76848
12
13
14
15	Implicit interest rate - effectively the Internal Rate of Return	11.28%
Sheet1

Excel 2003

Cell Formulas
Range	Formula
B11	=-61303-15545
B15	=XIRR(B1:B11,A1:A11)