Note: Examples can also be seen by downloading "seeka loan calc.xls" (click here) from
https://app.box.com/s/kbc9y71brc6y8dt9p0wf8j898wt4dpuf
If you put those payments into a cashflow schedule (columns D&E), the first thing I need is a formula for E16 that looks at columns D&E and gets us back to a return of 7%, the current XIRR formula is giving me 7.21% [sic; errata: 7.27%]
=XIRR(E1:E14,D1:D14,B1)
That would be:
=
12*IRR(G2:G14)
where column G contains the
net periodic cash flows. G2 is =E1+E2. G3 is =E3; G4 is =E4; etc. Excel IRR assumes each cash flow is in a different period.
See Schedule #1 in "seeka loan calc.xls".
When the (net) cash flows reflect only the financed amount, the IRR of the cash flows is about the same as the interest rate,
when both are calculated the same way.
Small, usually infinitesimal differences might arise due to binary arithmetic anomalies and rounding of actual payment amounts.
XIRR returns a different rate primarily because it is a
compounded ("effective") rate, whereas you use a
simple ("nominal") interest rate.
In theory, the
periodic XIRR calculated by =(1+XIRR(E1:E14,D1:D14))^(1/12)-1 or =RATE(12,0,-1,1+XIRR(E1:E14,D1:D14)) should be the same as the
periodic interest rate calculated by =B1/B2. They are different because the latter assumes equal periods, whereas XIRR uses the actual number of days in each period (28, 29, 30 or 31).
In theory, XIRR returns the APR in regions that follow EU regulations, including the UK; notably not the US, Canada and some other regions. But even that is slightly different, since the EU APR takes leap years into account.
What I I’d like to do is amend the customer’s payments so that they pay the same monetary amount overall but more finance in the early years and less towards the end which increases the yield to the lender because the balance is reducing quicker.
The sum of the customer payments is not "the same monetary amount". In your original example of (600, 9*200, 2*0 and 100), the total payment is 2500. But for the normally-amortized lease, the sum of the payments is 2524.46 (12*202.04 and 100).
See Schedule #1 and #2 in "seeka loan calc.xls".
The payment for the normally-amortized lease is =PMT(B1/B2,B5,-B3,B4,1).
Moreover, the "yield" is not increased due to increased payments ("more finance") in earlier years.
The annualized IRR is always the same as the (simple) annual interest rate. So the "yield" is not increased. The only way to increase the "yield" (rate of return) is to change some input parameters, for example: shorten the payback term, or increase the annual interest rate.
Moreover, the total interest over time is reduced: 2500-B3 for the original example v. 2524.46-B3 for the normally-amortized lease.
the following formula in B7 calculates the rental and it seems to be accurate although I can’t profess to understand how it works, I found it on online!
=IF(B6>10,"Too Many Advance Payments",(B3-B4/(1+B1/B2)^B5)/((1-(1+B1/B2)^(-(B5-B6)))/(B1/B2)+B6))
The formula is specific to the nature of the payments. In particular, it calculates the
fixed (equal) periodic payment assuming payment "in advance" like most leases (v. "in arrears" like most loans) with the first B6 payments at the beginning of the loan term, followed by B5-B6 equal payments, B6-1 periods of no payment, and an ending balance of B4, paid after end of the lease term.
The formula algebraically solves for PMT in the general loan equation; see the Excel PV help page. To demonstrate, the payment can be calculated using:
=PMT(B10,B5-B6+1,-B3+B7*(B6-1),B4/(1+B0)^(B6-1),1)
where B10 is the periodic interest rate =B1/B2.
Of course, the term B7*(B6-1) presumes foreknowledge of the fixed payment. But we could substitute B7*(B6-1) with any arbitrary amount that we want to add to the first payment ("in advance").
The term B4/(1+B10)^(B6-1) discounts the ending balance (B4) to the period of the last non-zero payment, accounting for B6-1 periods of no payment. That is, it is the present value of the ending balance as of that period.
What I I’d like to do is amend the customer’s payments so that they pay [....] For example
Year 1 Finance £220 Maintenance £80 total customer payment £300
Year 2 Finance £200 Maintenance £100 total customer payment £300
Year 3 Finance £185 Maintenance £115 total customer payment £300 etc
[....]
Once I know the formula works, I can change the payments in E2-E11 , I’ll get a new yield in E16 that I can put back through the first formula (B7) to get a new rental, I can then apply the original yield (7%) to the new rental and get a either an increased invoice cost or smaller balloon which is where I need the 2nd and 3rd formulas to solve for cost and balloon.
Without using VBA, I do not believe we can use a formula to derive a list of arbritrary payments, unless there is a mathematical relationship among them; for example, a fixed ratio or a fixed difference. Even then, the math might be challenging, if not intractable.
Instead, I suggest that you can use Schedule #3 in "seeka loan calc.xls" as a template, shown below.
| D
| E
| F
| G
| H
| I
| J
| K
|
1
| SCHEDULE #3 |
|
|
|
| | | |
2
|
| CF | Bal | Net CF | Pmt # | Min Pmt | Amort Pmt |
|
3
| 1/26/2015 | -2441.86 | 2441.86 |
|
|
|
|
|
4
| 1/26/2015 | 600.00 | 1852.60 | -1841.86 | 1 | 14.17 | 202.04 |
|
5
| 2/26/2015 | 200.00 | 1662.24 | 200.00 | 2 | 10.75 | 164.58 |
|
6
| 3/26/2015 | 200.00 | 1470.77 | 200.00 | 3 | 9.65 | 160.92 |
|
7
| 4/26/2015 | 200.00 | 1278.19 | 200.00 | 4 | 8.53 | 156.46 |
|
8
| 5/26/2015 | 200.00 | 1084.48 | 200.00 | 5 | 7.42 | 150.87 |
|
9
| 6/26/2015 | 200.00 | 889.64 | 200.00 | 6 | 6.29 | 143.69 |
|
10
| 7/26/2015 | 200.00 | 693.66 | 200.00 | 7 | 5.16 | 134.11 |
|
11
| 8/26/2015 | 200.00 | 496.54 | 200.00 | 8 | 4.03 | 120.70 |
|
12
| 9/26/2015 | 200.00 | 298.27 | 200.00 | 9 | 2.88 | 100.58 |
|
13
| 10/26/2015 | 200.00 | 98.84 | 200.00 | 10 | 1.73 | 67.05 |
|
14
| 11/26/2015 | 0.00 | 99.42 | 0.00 | 11 | 0.00 | 0.00 |
|
15
| 12/26/2015 | 0.00 | 100.00 | 0.00 | 12 | 0.00 | 0.00 | reqd last pmt |
16
| 1/26/2016 | 100.00 |
| 100.00 |
|
|
|
|
17
|
|
|
|
|
|
|
|
|
18
|
| 7.27% | xirr |
|
|
|
|
|
19
|
| 0.5867% | periodic xirr |
|
|
|
|
|
20
|
|
| periodic irr | 0.5833% |
|
|
|
|
21
|
|
|
| 5.63E-13 | error |
|
|
|
22
|
|
| annual rate | 7.0000% |
|
|
|
|
23
|
|
| init cost | 2441.86 |
|
|
|
|
<tbody>
</tbody>
Code:
Formulas:
E3: =-F3
E15: =J15
E16: =F15
F3: =$B$3
F4: =(F3-E4)*(1+$B$10)
Copy F4 into F5:F15
G4: =E4+E3
G5: =E5
Copy G5 into G6:G16
I4: =MIN(J4,ROUNDUP(F3*$B$10/(1+$B$10),2))
Copy I4 into I5:I14
I15: =J15
J4: =PMT($B$10,$B$2-H4+1,-F3,$B$4,1)
Copy J4 into J5:J15
E18: =XIRR(E3:E16,D3:D16)
E19: =RATE(12,0,-1,1+E18)
G20: =IRR(G4:G16)
G21: =G20-$B$10-0
G22: =12*G20
G23: =NPV(B10,E5:E16)+E4
Column D is not needed if you dispense with the XIRR calculations. (I think you should.)
You might fill in arbitrary payment amounts in E4:E14 to determine the last payment in E15 based on input parameters in B1:B5.
Alternatively, overwrite the formula in E15 with an arbitrary last payment amount to determine a new ending balance in E16.
And overwrite the formula in E16 with an arbitrary ending balance to determine a new annual rate (G22) or initial cost (G23).
The formula in E15 (last payment) is =J15, where J15 is =PMT($B$10,$B$2-H15+1,-F14,$B$4,1).
The formula in E16 is =F15, where F15 is =(F14-E15)*(1+$B$10).
The formula in G22 is =12*G20, where G20 is =IRR(G4:G16).
The formula in G23 is =NPV(B10,E5:E16)+E4.