Calculating rate with variable payments.

[Splinter89]

Hi, I currently use the RATE() function in Excel to calculates rates before entering details in SAP. However this only works if the payments are the same every month, which is not always the case. Here are two common examples I would like to be able to calculate:

Variables:
60 months
£50,000 pv
£2,500 fv
£1,000/month
Billing in advance
(Excel gives a rate of 9.11%)

Example 1: "Three in advance"
1st month is 3x standard payment - £3k
Months 2-58 are standard - £1k
Months 59-60 are free.
With this profile, SAP gives a rate of 9.76%

Example 2: Three month discount
Months 1-3 are free
Months 4-60 are standard - £1k
With this profile, SAP gives a rate of 6.66%

Any ideas?


In case it's not clear, I want to calculate these rates in excel rather than relying on SAP.

 

[joeu2004]

Presumably, you derived the first rate a formula of the form =12*RATE(60,-1000,50000,-2500,1). The exact rate is 9.11217085950728%.

(Not everyone derives the annual rate by multiplying by 12.)

The only way that I am able to derive the other rates is with a monthly amortization table. I presume you have a typo, and the rate for example #1 should be 9.79%, not 9.76%.

Example #1

	B	C	D	E
1	Pmt#		9.79%	$50,000.00
2	1	$3,000.00	$383.37	$47,383.37
3	2	$1,000.00	$378.34	$46,761.71
58	57	$1,000.00	$28.15	$3,479.77
59	58	$1,000.00	$20.23	$2,500.00
60	59	$0.00	$0.00	$2,500.00
61	60	$0.00	$0.00	$2,500.00

<tbody>
</tbody>

Columns C, D and E should be titled Pmt, Int and Bal respectively.

Formulas:

D2: =(E1-C2)*$D$1/12
E2: =E1-C2+D2
Copy D2:E2 into D3:E61
D60: 0
D61: 0

Solver set-up:
Objective: E61
To Value of: 2500
By Changing: D1

Initially, the exact result is 9.78820354354627%. That can be improved by changing options, or simply by running Solver again, leaving the first derived rate in D1. The improved exact result is 9.78819943900575%

Example #2 :

	G	H	I	J
1	Pmt#		6.66%	$50,000.00
2	1	$0.00	$277.70	$50,277.70
3	2	$0.00	$279.24	$50,556.94
4	3	$0.00	$280.79	$50,837.74
5	4	$1000.00	$276.80	$50,114.54
60	59	$1000.00	$19.26	$3,486.19
61	60	$1000.00	$13.81	$2,500.00

<tbody>
</tbody>

Columns H, I and J should be titled Pmt, Int and Bal respectively.

Formulas:

I2: =(J1-H2)*$I$1/12
J2: =J1-H2+I2
Copy I2:J2 into I3:J61

Solver set-up:
Objective: J61
To Value of: 2500
By Changing: I1

Initially, the exact result is 6.66482509932463%. The improved exact result is 6.66482155818541%.

Note that the first 3 months are not "free"; interest accrues. You simply make no payments for 3 months.

If the first 3 months were truly free (no interest as well), the derived annual rate would be 7.3938912912066%. Set I2:I4 to zero to solve.

 

[joeu2004]

PS....

I presume you have a typo, and the rate for example #1 should be 9.79%, not 9.76%.

Arguably, 9.76% might not be a typo. Theoretically, differences might arise depending on if and how the lender (or SAP) rounds interim calculations. That varies from lender to lender. Obviously, my calculations do no rounding. I am not bothering to experiment with the various alternatives to see if I might get approximately 9.76% [sic].

 

[joeu2004]

I presume you have a typo, and the rate for example #1 should be 9.79%, not 9.76%.
[....]
Example #1
[....]
D60: 0
D61: 0
[....]
Example #2
[....]
Note that the first 3 months are not "free"; interest accrues. You simply make no payments for 3 months.

My "mistake" -- sort of.

For example #1 , I had interpreted "free" to mean no interest as well as no payments, as it should be.

But for example #2 , I realized that "free" does not mean "no interest". It simply means "no payments".

Now I realize that I should have applied the same definition to example #1 .

Do not set D60 and D61 to zero.

Instead, let the copied formulas calculate and add the monthly interest to the balance after payment #58 .

Voila! The annual rate derived in D1 by Solver is indeed about 9.76%, as expected; exactly 9.76498470112743%.

The resulting table for example #1 is:

	B	C	D	E
1			9.76%	$50,000.00
2	1	$3,000.00	$382.46	$47,382.46
3	2	$1,000.00	$377.44	$46,759.90
58	57	$1,000.00	$27.77	$3,439.95
59	58	$1,000.00	$19.86	$2,459.80
60	59	$0.00	$20.02	$2,479.82
61	60	$0.00	$20.18	$2,500.00

<tbody>
</tbody>

Note the key changes, highlighted in red.

-----

I hasten to point out that I am only demonstrating how to duplicate the SAP results.

But the SAP results might be wrong. GIGO!

If the lender says "no payment", and you added the word "free" (a misunderstanding), the SAP model and results are correct, as is the amended Excel implementation.

But if the lender does indeed say "free", I'm sure that he means "no interest" as well as no payment. Confirm with the lender.

In that case, the SAP model is wrong; and concomitant corrections are needed for the Excel model.

I have already alluded to the corrections: force the interest payment to be zero in the periods that should be "free".

 

[Splinter89]

Thank you!! And sorry I was a little sloppy with my wording, I did indeed mean "no payments" rather than "free".

I was able to write a custom function based on your process, and now I can calculate the rate for any of these profiles with ease. I'll paste the function here in case anyone else finds this question and has similar needs:

Public Function VRATE(Nper As Double, Pmt As Double, PV As Double,  FV As Double, Due As Integer, Guess As Double, Profile As Integer)
'
Dim Payment() As Double
Dim Interest() As Double
Dim Balance() As Double
ReDim Payment(1 To Nper + 2 - Due)
ReDim Interest(1 To Nper + 2 - Due)
ReDim Balance(1 To Nper + 2 - Due)

    If Profile = 0 Then
        VRATE = Rate(Nper, Pmt, PV, FV, Due, Guess)
    Else
        Balance(1) = PV
        If Profile > 0 Then
            Payment(3 - Due) = -Pmt * Profile
            For i = 4 - Due To Nper - Profile + 3 - Due
                Payment(i) = -Pmt
            Next
            Lowerbound = Rate(Nper, Pmt, PV, FV, Due, Guess)
            Upperbound = Lowerbound * 2
        Else
            For i = 3 - Due - Profile To Nper + 2 - Due
                Payment(i) = -Pmt
            Next
            Upperbound = Rate(Nper, Pmt, PV, FV, Due, Guess)
            Lowerbound = 0 - Upperbound
        End If
        VRATE = (Upperbound + Lowerbound) / 2
        Count = 0
        While Upperbound - Lowerbound > 0.000000001 And Count < 30
            Count = Count + 1
            VRATE = (Upperbound + Lowerbound) / 2
            For i = 2 To Nper + 1
                Interest(i) = (Balance(i - 1) - Payment(i)) * VRATE
                Balance(i) = Balance(i - 1) - Payment(i) + Interest(i)
            Next
            If Due = 0 Then
                Balance(Nper + 2) = Balance(Nper + 1) - Payment(Nper + 2) + Interest(Nper + 2)
            End If
            If Balance(Nper + 2 - Due) > -FV Then
                Upperbound = VRATE
            Else
                Lowerbound = VRATE
            End If
        Wend
    End If
End Function

It works exactly the same as rate, except there is an extra parameter at the end. Here you can enter 0 for the standard rate calculation, a positive integer for payments in advance, or a negative integer for a payment holiday.

For the examples in my first post, the formula would be:
Ex 1: =VRATE(60,-1000,50000,-2500,0.01,3)*12
Ex 2: =VRATE(60,-1000,50000,-2500,0.01,-3)*12

Thanks again joeu for a great and detailed answer!

 

[Splinter89]

Just in case anyone does use that function, I should mention it's not perfect. I wasn't sure how to set appropriate upper/lower bounds on the estimate, so with really crazy profiles (like 24 months in advance on a 60 month contract) it can be wrong.

 

[joeu2004]

When we corrected my understanding of "free" v. "no payments", I should have revisited the problem. Now, I realize that we can use Excel IRR instead of Solver to derive the interest rate. However, the IRR model is a little tricky because payments are "in advance". Moreover, your VBA solution allows for payments "in arrears", as well, which uses a different (more typical) IRR model. Let me know if you are interested in understanding the Excel IRR approach.

 

[Splinter89]

I would definitely be interested in learning the IRR approach if you are willing to demonstrate

 

[joeu2004]

First, consider your examples with payments "in arrears". This demonstrates the normal way that we think of using Excel IRR.

	A	B	C	D	E	F	G	H
1			9.416326%	annl rate			6.460891%	annl rate
2			$2,500.00	fv			$2,500.00	fv
3			$50,000.00	pv			$50,000.00	pv
4
5	pmt#	pmt	bal		pmt#	pmt	bal
6			$50,000.00				$50,000.00
7	1	$3,000.00	$47,392.35		1	$0.00	$50,269.20
8	2	$1,000.00	$46,764.23		2	$0.00	$50,539.86
9	3	$1,000.00	$46,131.19		3	$0.00	$50,811.97
10	4	$1,000.00	$45,493.18		4	$1,000.00	$50,085.54
64	58	$1,000.00	$2,461.22		58	$1,000.00	$4,457.26
65	59	$0.00	$2,480.54		59	$1,000.00	$3,481.26
66	60	$0.00	$2,500.00		60	$1,000.00	$2,500.00
67			-3.37E-10	error			-5.98E-10	error

<tbody>
</tbody>

Formulas:
C1:  { =12*IRR(IF(ROW(B6:B66)=ROW(B6),-C6,IF(ROW(B6:B66)=ROW(B66),B66+C2,B6:B66))) }
C6:  =C3
C7:  =C6*(1+C$1/12)-B7
C67: =C66-C2
Copy C7 into C8:C66

Copy C1 into G1
Copy C6 into G6
Copy C7:C67 into G7:G67

Formulas displayed with curly brackets {...} are array-entered.   Type the formulas
without the curly brackets, then press ctrl+shift+Enter instead of just Enter.
Excel displays the curly brackets in the Formula Bar to indicate that the formula
is array-entered.

Essentially, we calculate IRR(B6:B66). But....
1. The first cash flow is -PV (-C6).
2. The second cash flow is the first payment (B7).
3. The last cash flow is FV (C2) plus the last payment (B66).
4. The new balance (C7) is the previous balance plus interest based on the previous balance, C6*(1+C1/12); then payment (B7) is subtracted.

Now, contrast that with the use of Excel IRR with payments "in advance".

	I	J	K	L	M	N	O	P
1			9.764986%	annl rate			6.664822%	annl rate
2			$2,500.00	fv			$2,500.00	fv
3			$50,000.00	pv			$50,000.00	pv
4
5	pmt#	pmt	bal		pmt#	pmt	bal
6			$50,000.00				$50,000.00
7	1	$3,000.00	$47,382.46		1	$0.00	$50,277.70
8	2	$1,000.00	$46,759.90		2	$0.00	$50,556.94
9	3	$1,000.00	$46,132.27		3	$0.00	$50,837.74
10	4	$1,000.00	$45,499.53		4	$1,000.00	$50,114.54
64	58	$1,000.00	$2,459.80		58	$1,000.00	$4,466.94
65	59	$0.00	$2,479.82		59	$1,000.00	$3,486.19
66	60	$0.00	$2,500.00		60	$1,000.00	$2,500.00
67			-5.36E-10	error			-4.54E-10	error

<tbody>
</tbody>

Formulas:
K1:  { =12*IRR(IF(ROW(J6:J66)=ROW(J6),-K6+J7,IF(ROW(J6:J66)=ROW(J66),K2,J7:J67))) }
K6:  =K3
K7:  =(K6-J7)*(1+K$1/12)
K67: =K66-K2
Copy K7 into K8:K66

Copy K1 into O1
Copy K6 into O2
Copy K7:K67 into O7:O67

Formulas displayed with curly brackets {...} are array-entered.   Type the formulas
without the curly brackets, then press ctrl+shift+Enter instead of just Enter.
Excel displays the curly brackets in the Formula Bar to indicate that the formula
is array-entered.

Essentially, we calculate IRR(J7:J67). But....
1. The first cash flow is -PV (-K6) plus the first payment (J7).
2. The second cash flow is the second payment (J8).
3. The last cash flow is just FV (K2).
4. The new balance (K7) is the previous balance plus interest based on the previous balance minus payment, (K6-J7)*(1+K1/12).

The key difference is: the payments are shifted into the cash flow of the previous period.

We could do a lot of complicated trickery in order to avoid the amortization schedule and to parameterize the options ("n" number of zero payments in the beginning or end).

But I hope this simple presentation highlights the difference in using Excel IRR for payments "in advance" v. "in arrears", as well as how to use Excel IRR with cash flows that are not all in single contiguous range.

 

[joeu2004]

Now, contrast that with the use of Excel IRR with payments "in advance".
[....]
Copy K6 into O2

I hope the typo is obvious: O6 instead of O2.

Since I'm here, I wish I had written:

Essentially, we calculate IRR(J7:J67). But....
1. The first cash flow is -PV (-K6) plus the first payment (J7).
2. The second cash flow is the second payment (J8).
3. The last-1 cash flow is the last payment (J66).
4. The last cash flow is just FV (K2).
5. The new balance (K7) is the previous balance plus interest based on the previous balance minus payment, (K6-J7)*(1+K1/12).