Solving for variable CAT

[ldorgan]

Please see the equation at the following link (it is an image):

CAT Equation

. . .\(\displaystyle \L 0\, =\,F_0\,+ \,\frac{F_1}{(1\,+\,CAT)^{\frac{1}{P}}}\,+ \,\frac{F_2}{(1\,+\,CAT)^{\frac{2}{P}}}\,+\,...\,+ \,\frac{F_n}{(1\,+\,CAT)^{\frac{n}{P}}}\)

Can someone help me solve this equation for CAT? Below is information on the equation:

The CAT will be the annual interest rate that makes equal to zero the sum of the amounts included in the amortization table discounted to present value for such interest rate.

F0 = Amount of Loan, fees and payments that the customer pays before or at the beginning of the contract sign

Fk = Amount that the borrower will pay in the k period, where k = 1, 2, ..., n as is determined in the amortization table

n = Total pay periods along the loan tenure.

p = Number of payments in a year, for example, p = 12 implies monthly payments.

 

[JakeD]

CAT is called the Internal Rate of Return or IRR. There is no general formula for calculating it. But your spreadsheet may have an IRR function that will allow you to calculate it if you give it the numbers \(\displaystyle F_0, ..., F_n .\) \(\displaystyle F_0\) must be entered as a negative number.

For example, I entered -500, 100, 100, 200, 200 into my spreadsheet and fed those into the IRR function. It returned the value 6.74%. The IRR function requires an initial guess; I used 0.

To check the calculation, you can use the function NPV (for Net Present Value). Give the NPV function the same inputs and the interest rate returned by IRR. If everything is working correctly, NPV should return the value 0. That is the meaning of this sentence.

The CAT will be the annual interest rate that makes equal to zero the sum of the amounts included in the amortization table discounted to present value for such interest rate.

All of the above assumes the number \(\displaystyle p\) of payments per year is 1. If \(\displaystyle p \not = 1\), use \(\displaystyle CAT = (1 + IRR)^p - 1\) to convert.

 

[Denis]

You can look at it as a loan; using Jake's example:

0                500.00
1 -100 +33.70    433.70
2 -100 +29.23    362.93
3 -200 +24.46    187.39
4 -200 +12.61      0.00

That's a loan of $500 at 6.74% annual interest,
repayable annually at $100 for 1st 2 years,
then $200 for last 2 years.

 

[ldorgan]

Thanks Jake and Denis. Via spreadsheet, we have been able to calculate, although I've been told there can be values of CAT that solve this equation (in that case, we need the postive value of CAT closest to zero).

Here is my delemma, we are trying to solve for CAT programmatically in a system we are building, so using Excel's features are not really an option.

Any advice?

 

[Denis]

Well, methinks you're out of luck and need to do iteration,
similar to solving for i in this equation: p = ai / [1 - 1 / (1 + i)^n]

Googling "iteration calculation" will give you helpful sites, like:
http://www.ealnet.com/ealsoft/iteratio.htm

Btw, I don't see why you can't use (1 + i) instead of (1 + CAT);
minor point, but why make it look more complicated than it is?

 

[ldorgan]

"CAT" is a value that is required to be provided to borrowers of mortgage loans in Mexico. The equation, including the use of "CAT" as the variable, was taken from a document that the government in Mexico provides to lending institutions.

Thank you for your help.

 

[JakeD]

Thanks Jake and Denis. Via spreadsheet, we have been able to calculate, although I've been told there can be values of CAT that solve this equation (in that case, we need the postive value of CAT closest to zero).

Here is my delemma, we are trying to solve for CAT programmatically in a system we are building, so using Excel's features are not really an option.

Any advice?

Here's an on-line book with a number of financial calculations including IRR. It uses C++ as the programming language, but the algorithms are translatable to other languages if necessary.

 

[Denis]

"CAT" is a value that is required to be provided to borrowers of mortgage loans in Mexico. The equation, including the use of "CAT" as the variable, was taken from a document that the government in Mexico provides to lending institutions.

SO WHAT? I'm talking programming, not consumer disclosure terms.
Let i = CAT, same way as a = apples...