reverse engineer interest rate

[AceFI]

hi all.
i'm trying to work a reverse calculation on a finance repayment calculator.
i have have the standard amortisation calcs..

$20k amount
84 months
10% interest
$332 repayments per month
Total interest component of $7890

If i bump the repayments up to $375 per month, it lowers the loan term to about 71 months, and interest of $6552

Difference 13 months, $1338 interest

I'm trying to work out how to show the saving / difference relative back to an interest rate....
i've tried =rate using the original term, higher payment, and new term new payment however i can't get it to output what i'm after.

any suggestions?

Outcome is to show someone 'by paying an extra $43 per month off your loan, the interest rate would be relative to xx%'

thanks

 

[joeu2004]

First, you are mixing up some terminology: the total interest changes, but presumably not the interest rate per se. At least, you have not provided anything to suggest that the interest rates are different.

So, the difference in total interest does not reflect a "difference relative to an interest rate" per se.

If you want to say "we save x% in total interest", we would calculate newTotal/oldTotal - 1. Ostensibly, that is 6552/7890 - 1, which is about -16.96%. (Read: 16.96% savings or less.)

Second, we can never expect to duplicate a lender's calculation exactly, because we do not know how they round amounts, especially in an amortization schedule. We can only hope to come close.

-----

In your first example, the payment can be calculated by =PMT(10%/12,84,-20000). The result is about 332.02; more precisely, 332.023680537718.

Total interest is calculated by pmt*nper - pv. 332*84 - 20000 = 7888, not 7890. 332.02*84 - 20000 = 7889.69, which rounds to 7890. But 332.023680537718*84 - 20000 = 7889.99, which is even closer to 7890.

-----

In your second example, there seems to be some inconsistencies.

Ostensibly, the total interest is 375*71 - 20000 = 6625, not 6552. Is the latter just a typo?

If so, the annual interest rate is =12*RATE(71,375,-20000). That is 10.0663203564279%, which we can round to 10.066%.

(IMHO, the annual rate really did not change. It only appears to change because we are using rounded amounts.)

Alternatively, with payments of 375, the number of payments can be calculated by =NPER(10%/12,375,-20000). The result is 70.8278866273698. That's 70 regular payments plus an "odd period". Lenders handle the "odd period" differently.

One way.... The balance after 70 payments is =FV(10%/12,70,375,-20000) = 308.111407553028. The interest on that balance is 308.111407553028*10%/12 = 2.5675950629419. So, the final payment is the sum, or more accurately: =ROUND(FV(10%/12,70,375,-20000)*(1+10%/12),2) = 310.68.

Thus, the total interst is 375*70 + 310.68 - 20000 = 6560.68, which rounds to 6561, still not 6552.

Alternatively, if we assume 71 full payments, the payment would be =PMT(10%/12,71,-20000). The result is about 374.33, not 375. And the total interest would be 374.33*71 - 20000 = 6577.43, not 6552.

On the other hand, if we assume that total interest is 6552, the total payments would be 6552 + 20000 = 26552. Then, the monthly payment would be 26552/71 = 373.97 (approx). That rounds to 374, not 375.

 

[joeu2004]

PS....

Alternatively, if we assume 71 full payments, the payment would be =PMT(10%/12,71,-20000). The result is about 374.33, not 375. And the total interest would be 374.33*71 - 20000 = 6577.43, not 6552.

On the other hand, if we assume that total interest is 6552, the total payments would be 6552 + 20000 = 26552. Then, the monthly payment would be 26552/71 = 373.97 (approx). That rounds to 374, not 375.

But if we assume 71 payments of 374 (not 375; a typo?), the total interest is 374*71 - 20000 = 6554. Still not 6552; but perhaps close enough to assume that the difference is due to differences in lender methodology.

 

[joeu2004]

Please forgive the incessant responses, but I should mention one other issue, although I do not believe it applies.

Note that I calculated the monthly rate as 10%/12. That is valid for mortgages in many places in the world. It is also true of other kinds of loans in the UK and Canada, IIRC.

But in the UK and EU, the monthly rate for mortgages per se is (1+10%)^(1/12)-1. So for the first example, the monthly payment is =PMT((1+10%)^(1/12)-1,84,-20000) = 327.59.

In Canada, the monthly rate for mortgages per se is (1+10%/2)^(1/6)-1. And the monthly payment is =PMT((1+10%/2)^(1/6)-1,84,-20000) = 329.94.

I do not believe that either of those alternatives applies because the calculated monthly payment is so different 332.

In contrast, as I showed previously, with 10%/12, the monthly payment is =PMT(10%/12,84,-20000) = 332.02, which does indeed round to 332.

Finally, if you give use the URL for the online(?) "finance repayment calculator", we might be more helpful with fewer digressions and distractions.

 

[AceFI]

wow!! what a response !! thanks!!
however i don't think what i was chasing wasn't clear.

what i'm trying to show someone, is
'here are your repayment terms at x% interest (10% in my example) your repayment (rounded) is $332 per month. over 84 months this equate to $7890 interest'
'If you were to bump your repayments up to $375 per month, your interest component will only be 6552'
This is a saving of 16.96% (from your math - thanks for advising this calc )

however, what i'm trying to achieve is is making the interest saving, relative to a calculated interest rate.

the 'end game' here is if a consumer has a loan at 10% however isn't happy with 'that' then then suggestion is to make extra payment. infact, if you pay an extra $43 per month, you will save $1338 in interest. This saving is relative to the interest rate being xx% "
(the $6552 in interest has been worked off amortising the loan, not a straight 71 month equal term

 

[joeu2004]

the $6552 in interest has been worked off amortising the loan, not a straight 71 month equal term

In order for me to comment further, I need:

1. The URL of the online(?) loan calculator, and what you entered into any input fields for both loan options; and/or

2. Images of the complete amortization schedules for both loan options. The images must include what you entered into any input fields.

This forum does not allow for such files to be attached to the posting. So for #2 , please upload the files to a file-sharing website, and include the public/share URL in your response.

For my example below, I assume that the amortization is based on the actual days between payments (instead of "monthly" payments) and on the daily interest rate (instead of the monthly rate).

But there are so many different ways that such an amortization schedule might be calculated. That is why I need to use the calculator myself or at least to see the details of the calculator results.

-----

what i'm trying to achieve is is making the interest saving, relative to a calculated interest rate.
[....]
if you pay an extra $43 per month, you will save $1338 in interest. This saving is relative to the interest rate being xx%

You missed my previous point: the savings in total interest is simply and likely due to the fewer and larger payments, probably not to a different interest rate.

Although it is possible that a lender would offer different interest rates for different loan terms, the example below demonstrates that it is not necessarily the case.

	A	B	C	D	E	F	G	H	I	J	K
1	Loan	$20,000.00					Loan	$20,000.00
2	Annl rate	10.00%					Annl rate	10.00%
3	#Pmts	84					#Pmts	71
4	Pmt	$332.13					Pmt	$375.49
5
6			Pmt	Int	Bal				Pmt	Int	Bal
7	TOTAL		$27,890.34	$7,890.34			TOTAL		$26,552.31	$6,552.31
8		6/16/2019			$20,000.00			6/16/2019			$20,000.00
9	1	7/16/2019	$332.13	$164.38	$19,832.25		1	7/16/2019	$375.49	$164.38	$19,788.89
10	2	8/16/2019	$332.13	$168.44	$19,668.56		2	8/16/2019	$375.49	$168.07	$19,581.47
11	3	9/16/2019	$332.13	$167.05	$19,503.48		3	9/16/2019	$375.49	$166.31	$19,372.29
12	4	10/16/2019	$332.13	$160.30	$19,331.65		4	10/16/2019	$375.49	$159.22	$19,156.03
76	68	2/16/2025	$332.13	$44.49	$4,950.34		68	2/16/2025	$375.49	$11.62	$1,003.71
77	69	3/16/2025	$332.13	$37.98	$4,656.18		69	3/16/2025	$375.49	$7.70	$635.92
78	70	4/16/2025	$332.13	$39.55	$4,363.60		70	4/16/2025	$375.49	$5.40	$265.83
79	71	5/16/2025	$332.13	$35.87	$4,067.34		71	5/16/2025	$268.01	$2.18	$0.00
89	81	3/16/2026	$332.13	$9.92	$971.52
90	82	4/16/2026	$332.13	$8.25	$647.64
91	83	5/16/2026	$332.13	$5.32	$320.83
92	84	6/16/2026	$323.55	$2.72	$0.00
93

<tbody>
</tbody>

Formulas:
C7: =ROUND(SUM(C9:C93),2)
D7: =ROUND(SUM(D9:D93),2)
A9: =IF(A8 < B$3, A8+1, "")
B9: =IF(A9="", "", EDATE(B$8,A9))
C9: =IF(A9="", "", IF(A9 < B$3, B$4, ROUND(E8+D9,2)))
D9: =IF(A9="", "",
    E8*(B9-DAY(B9)-B8)*B$2/(365+OR(MOD(YEAR(B8),400)=0,AND(MOD(YEAR(B8),100) <> 0,MOD(YEAR(B8),4)=0)))
    + E8*DAY(B9)*B$2/(365+OR(MOD(YEAR(B9),400)=0,AND(MOD(YEAR(B9),100) <> 0,MOD(YEAR(B9),4)=0))))
E9: =IF(A9="", "", IF(A9 < B$3, E8+D9-C9, ROUND(E8+D9-C9,2)))
Copy C7:D7 into I7:J7
Copy A9:E9 into A10:E93 and into G9:K93<b$3,e8+d9-c9,round(e8+d9-c9,2)))
<b$3,a8+1,"")
 <b$3,b$4,round(e8+d9,2)))
<b$3,e8+d9-c9,round(e8+d9-c9,2)))

Note that the same annual interest rate (10.00%) is used in both examples.

Note that regular payments round to $332 and $375 respectively, and total interest rounds to $7890 and $6552 respectively.

In both examples, the final payment differs from the regular payments in order to result in zero balance without incurring "finance charges" (additional interest) that are not based on the previous balance.

The amount of interest in both periods is the sum of interest for days in the previous month and interest for days in the current month. They are calculated separately to allow for the case when only one of the months is in a leap year.</b$3,e8+d9-c9,round(e8+d9-c9,2)))
</b$3,b$4,round(e8+d9,2)))
</b$3,a8+1,"")
The daily interest rate is annualRate/365 in normal years and annualRate/366 in leap years.</b$3,e8+d9-c9,round(e8+d9-c9,2)))

 

[Colmans]

I think you are over complicating it. The best way to demonstrate the time value money impacts to someone is through an amortisation schedule (similar to above). PM me with your e-mail and i'll send you one over.

The other alternative is to download the HP12C app for your i-phone which will do this calc easily for you. Note that the HP12C rounds up months, where as a current HP17BII will solve the number of months in your calc to 70.83 (assuming payments in arrears)

 

[AceFI]

trying to do it for the purpose of an excersize.. let me rephrase in a different way.
If i know the loan term, the interest payable and the amount financed, is there a way to backward engineer the interest rate? (so items not present are repayment and interest rate) thanks.

 

[joeu2004]

If i know the loan term, the interest payable and the amount financed, is there a way to backward engineer the interest rate? (so items not present are repayment and interest rate)

This will be my final contribution to this discussion, since you keep changing the problem. Ironically, this is the problem that I thought you might be interested in solving.

Yes, it can be done -- to some degree. But with two inter-dependent variables, there are many solution. Moreover, the solution depends on the amortization model.

As I explained before, using your second example, if the principal loan ("amount financed") is $20,000, there are 71 payments ("loan term"), and the total interest is $6552 ("interest payable"), the regular (or average) payment is (20000+6552)/71 = $373.971830985916.

You can choose how to round that. But for whatever precision you choose, I suggest that you round up in order to ensure that the last payment is no more than the regular payment.

You might round up to $373.98, $374, $375 (multiple of $5), etc. I will assume $374.

In a simple amortization model, which assumes equal "monthy" payments (30/360 day-count basis), the interest rate is =12*RATE(71, 374, -20000), which is 9.96699242996182%.

However, based on your previous data, I believe your (onlilne?) loan calculator uses an amortization model based on the daily interest rate and the actual days between payments. I had also determined that the loan calculator uses a different daily interest rate for normal and leap years. (Otherwise, the rounded amounts for the variables were not consistent with your original requirements.)

There is no Excel function that can estimate an interest rate for that model.

Instead, you can use Solver with the amortization model that I posted in response #6 .

Enter $20,000 into H1, 71 into H2, $374 into H3, and a loan date (6/16/2019) into H8. Delete any value in H2, the derived interest rate, since Solver is influenced by the initial value.

(Note: An amortization model like this is sensitive to the loan date. If you choose a loan date in a different month, you might get a very different result.)

Then, the Solver set-up would be:
Set: J7
To Value: 6552
By Changing: H2
Click Solve

In my Excel (Excel 2010), Solver derives an interest rate of 9.96081722659562%. (Very close to the 30/360 model.)

Caveat: the Solver algorithms might change from one version of Excel to another; your version might derive a different result. And even within one Excel version, sometimes Solver derives different results with the same starting values(!).

Also note that Solver does not tolerate explicit rounding well. It would be prudent to remove all rounding in the amortization model. (I got lucky with the little rounding that I have.)

Finally, in theory, Solver can derive both a regular payment (unrounded!) as well as an interest rate. But in my model, the solution is too trivial without additional constraints. And remember: there are an "infinite" number of solutions (many pairs of regular payments and interest rates).

 

[Colmans]

trying to do it for the purpose of an excersize.. let me rephrase in a different way.
If i know the loan term, the interest payable and the amount financed, is there a way to backward engineer the interest rate? (so items not present are repayment and interest rate) thanks.

AceFI - Joeu2004 gives a very good response. I use some very sophisticated lease/loan software in my daily work and part of the challenge I have when training people is that there are lots of terms people use which just confuse and in most cases are not correct. Part of that stems from understanding (or lack of) the relationship between the components, and how the repayment and interest charges are being structured. This is why an amortisation schedule really helps as it shows the maths step by step.