Loan Amount Formula

Seeka

New Member
Joined
Jan 21, 2015
Messages
13
Hi there
I'm looking for a loan formula that calculates the loan amount based an a payment schedule that is irregular. For example if you have a loan amount of £10,000 over 5 years at 7% giving a monthly payment of £242.72, I need formula that calculates what would happen to the £10k initial loan if you made the payments £250 in year one, £240 in year 2 etc. PV doesn't seem to work with irregular payments and I've tried to use XNPV but it doesn't return accurate data. I'd appreciate any help, I've been Googling for hours! Many thanks
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
What you need is a spreadsheet, not just a formula. The principal of a row is the balance of the previous row. Principal + interest - payment is the balance. This way, you can vary payment monthly and the spreadsheet can still calculate the correct interest and principal.
 
Upvote 0
What you need is a spreadsheet, not just a formula. The principal of a row is the balance of the previous row. Principal + interest - payment is the balance. This way, you can vary payment monthly and the spreadsheet can still calculate the correct interest and principal.

Hi. Thank you for the response. Forgive my ignorance but how would that work? What you're describing sounds like an amortization schedule, is that correct? I need to be able to adjust the payments and in doing so increase/decrease the initial loan that's on offer rather than the outstanding balance.
 
Upvote 0
I am sorry. I misunderstood your question. Just curious, how would one "increase/decrease the initial loan"? Once a loan is made, isn't the loan amount already fixed? Can one go back to adjust the initial loan one year after the loan has been taken out?
 
Upvote 0
I'm looking for a loan formula that calculates the loan amount based an a payment schedule that is irregular. For example if you have a loan amount of £10,000 over 5 years at 7% giving a monthly payment of £242.72, I need formula that calculates what would happen to the £10k initial loan if you made the payments £250 in year one, £240 in year 2 etc. PV doesn't seem to work with irregular payments

242.72 seems inconsistent with the other terms of the loan. I calculate about 198.01 or 197.00, depending on whether 7% is a simple or compounded annual rate, to wit:
Code:
198.01 = PMT(7%/12, 5*12, -10000)
197.00 = PMT(RATE(12,0,-1,1+7%), 5*12, -10000)
But I do not believe that matters.

I need to be able to adjust the payments and in doing so increase/decrease the initial loan that's on offer rather than the outstanding balance.

Your requirements are unclear. Can you cobble together an example Excel file that demonstrates what you want to calculate, and what features you need (i.e. what might change)?

Upload the Excel file to a file-sharing website and post the public/share URL in a response here. See footnote [1] for a list of some free file-sharing websites, if you need it.

For example, does the following reflect what you want to do?


A
B
C
D
1
Loan
11,685.81

2
Annl rate7.0000%

3
Mnthly rate0.5833%

4
Term60months
5
YearMnthlyPmtBeg Bal
6
125011,685.81
7
22409,432.43
8
32307,140.08
9
42204,805.94
10
52102,427.00
11


0End Bal

<tbody>
</tbody>
Code:
Formulas:
C10: =-PV($B$3,12,B10,C11)
C9:  =-PV($B$3,12,B9,C10)
C8:  =-PV($B$3,12,B8,C9)
C7:  =-PV($B$3,12,B7,C8)
C6:  =-PV($B$3,12,B6,C7)
B3:  =B2/12
B1:  =-myPV(B3,B4,B6:B10)
We derive the initial loan (C6) by calculating the beginning balance of each year, starting with the last year (C10), based on the monthly payment for each year.

I presume you would prefer to calculate the initial loan in B1 using a single formula. There might be a way to do that using standard Excel functions. In lieu of that, I created my own VBA function, myPV. See the code below.

The parameters are similar to the Excel PV function, to wit:

myPV(rate, nper, pmt [, fv])

The difference is: "pmt" is a range that specifies the list of monthly payments that change annually.

Notes.... "fv" is optional (default: fv=0). myPV assumes payments are monthly and in arrears (type=0). So "nper" must be a number of months; and "rate" must be a monthly rate. myPV, payments and "fv" are signed cash flows, following the Excel convention.
Code:
Function myPV(myRate As Double, myNPer As Double, myPmt As Range, _
        Optional myFV As Double = 0) As Double
    Dim nv As Long, nr As Long, nc As Long, i As Long, j As Long
    Dim n As Long, x As Double, t As Variant

    t = myPmt
    If TypeName(t) = "Double" Then
        ' myPmt.Count = 1
        ReDim v(1 To 1) As Double
        v(1) = t
        nv = 1
    Else
        ' copy myPmt into v() column by column
        nr = UBound(t, 1)
        nc = UBound(t, 2)
        ReDim v(1 To nr * nc) As Double
        nv = 0
        For i = 1 To nc: For j = 1 To nr
            nv = nv + 1
            v(nv) = t(j, i)
        Next j, i
    End If
    
    n = WorksheetFunction.RoundUp(myNPer / 12, 0)
    x = myNPer - 12 * (n - 1)
    myPV = WorksheetFunction.PV(myRate, x, v(n), myFV)
    For i = n - 1 To 1 Step -1
        myPV = WorksheetFunction.PV(myRate, 12, v(i), -myPV)
    Next
End Function


-----
[1] Dropbox: http://dropbox.com
Box.Net: http://www.box.net/files
MediaFire: http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidShare: http://www.rapidshare.com
 
Upvote 0
PS....

A
B
C
D
1
Loan
11,685.81

2
Annl rate7.0000%

3
Mnthly rate0.5833%

4
Term60months
5
YearMnthlyPmtBeg Bal
6
125011,685.81
7
22409,432.43
8
32307,140.08
9
42204,805.94
10
52102,427.00
11


0End Bal

<tbody>
</tbody>
[....]
We derive the initial loan (C6) by calculating the beginning balance of each year, starting with the last year (C10), based on the monthly payment for each year.

I presume you would prefer to calculate the initial loan in B1 using a single formula. There might be a way to do that using standard Excel functions.

Well, of course:
Code:
=-PV($B$3,12,B6,-PV($B$3,12,B7,-PV($B$3,12,B8,-PV($B$3,12,B9,
 -PV($B$3,B4-12*(ROUNDUP(B4/12,0)-1),B10,C11)))))
But that presumes a term of 49 to 60 months; that is, 5 nested PV calls. I was thinking of something more general.
 
Upvote 0
Hi there, thank you for the responses, I’m not sure where I got £242.72 from! I agree it should be £198.01.

What I’m trying to achieve is actually a lot more complicated than my initial email, I’m just trying to break it down into stages.

Ultimately I need an Operating Lease calculator whereby a lender buys equipment from a supplier (loan amount), Leases it to a customer and then sells it back to the supplier at the end of the customers agreement (effectively a balloon).

There is also maintenance included in the rental which is paid to the supplier and plays no part in the finance calculations ie finance repayment £200, Maintenance £100, total customer payment each month £300

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.

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

The benefit of this can then be switched so that rather than increasing the yield to the lender, it’s applied to help the supplier by increasing the invoice cost or decreasing the balloon/repurchase.

I’ve built a spreadsheet that can create a schedule of varied cashflows with dates, what I’m after is 3 formulas that can work off of this schedule, one to calculate yield, one to calculate invoice cost and one for the balloon (assuming you know 2 out of the 3 variables).

The built in Excel functions (XNPV, XIRR etc) don’t seem to give the desired results. I’d prefer to stay away from VBA if possible and use formulas.

My current spreadsheet works something like this;

A
B
C
D
E
1
Rate
7%

26/01/2015
-£2,441.86
2
Payment Frequency
12

26/01/2015
£600.00
3
Cost
£2,441.86

26/02/2015
£200.00
4
Balloon
£100.00

26/03/2015
£200.00
5
Term
12

26/04/2015
£200.00
6
Advance Payments
3

26/05/2015
£200.00
7
Rental
£200.00

26/06/2015
£200.00
8



26/07/2015
£200.00
9



26/08/2015
£200.00
10



26/09/2015
£200.00
11



26/10/2015
£200.00
12



26/11/2015
£0.00
13



26/12/2015
£0.00
14



26/01/2016
£100.00
15





16




7.21%

<tbody>
</tbody>

I enter data in cells B1 to B6 and 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))

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%

=XIRR(E1:E14,D1:D14,B1)

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.

I hope I’ve explained that properly! Thanks again
 
Upvote 0
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

CFBalNet CFPmt #Min PmtAmort Pmt
3
1/26/2015-2441.862441.86




4
1/26/2015600.001852.60-1841.86114.17202.04
5
2/26/2015200.001662.24200.00210.75164.58
6
3/26/2015200.001470.77200.0039.65160.92
7
4/26/2015200.001278.19200.0048.53156.46
8
5/26/2015200.001084.48200.0057.42150.87
9
6/26/2015200.00889.64200.0066.29143.69
10
7/26/2015200.00693.66200.0075.16134.11
11
8/26/2015200.00496.54200.0084.03120.70
12
9/26/2015200.00298.27200.0092.88100.58
13
10/26/2015200.0098.84200.00101.7367.05
14
11/26/20150.0099.420.00110.000.00
15
12/26/20150.00100.000.00120.000.00reqd last pmt
16
1/26/2016100.00
100.00



17








18

7.27%xirr




19

0.5867%periodic xirr




20


periodic irr0.5833%



21



5.63E-13error


22


annual rate7.0000%



23


init cost2441.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.
 
Upvote 0
Thank you so much for this. I'm going to be working on this project over the next week or so and will let you know how I get on (assuming you're interested of course!)
The level of your help has far exceeded my expectations when I posted this and I'm truly grateful.
 
Upvote 0
PS....
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 "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.

Perhaps "yield" is not the right term for what you intend to say.

It is true, as you noted, that with larger early payments, the lessor receives more money sooner. (Albeit less total money overall.)

That does increase the cash on hand in the shorter term that the lessor could use for other purposes, e.g. purchasing equipment for other operating leases. And that could increase the lessor's overall yield at the business level.

To that end, perhaps MIRR would be a better choice than simple "yield" for comparing alternative strategies, since that account for expected return on subsequent investment options.

Just one caveat.... Despite its name, MIRR has no relationship to (true) IRR and interest rates. It is simply a tool for comparing alternatives. So is "inches of cash". ;)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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