Excel financial functions
Thread Rating:
June 6th, 2013 at 1:25:50 PM
permalink
EXCEL TUTORIAL
Most people are familiar with the PMT (short for Payment) function in Excel or with a financial calculator. Really old school is to use a look up table with factors that you multiply by the amount you are borrowing. (30 year note at 4% has look up factor is .00477415 or $477.42 per month for every $100,000 you borrow). Then all that is required is an old fashioned calculator or hand multiplication.
Such tables are usually calculated for specific time periods, and for a range of interest rates that are considered meaningful at the time.
Table posted on The Mortgage Insider
Not everyone realizes that Excel has functions for all 5 unknown variables in the formula. It is possible to calculate each variable, which may save you time and effort hunting around.
Equations (1-3) are standard PMT calculations. One each for a 15 year, 30 year monthly, and 30 year biweekly. Future Value is set to zero, as most people calculate payments to eliminate a loan in a certain time. You can input a variable if you are trying to reduce it to a certain amount in a given time period. PMT is calculated as a negative number to indicate the financial convention that it is an expense.
Equation (4) is a common use of Future Value (FV). You have a loan, and you want to know the remaining principal after a certain time period. In this case I used the inputs from Equation (1) to see what I owed halfway through the mortgage. FV defaults to a negative number since it is money you owe.
Equation (5) is a degenerate case. I am just showing that the equations should work with otherwise simple solutions. The money put away in 15 years would be 15*12*PMT, but the formula produces the same result. This is the basis for your capital gains tax.
Equation (6) calculates the interest rate to get a PV required to pay a 25% capital gains tax and still have enough to pay off the mortgage using just the difference between the 15 year and 30 year payment
Equation (7) calculates the interest rate to get a PV=$100K. The rate calculated in equation #6 may be unrealistic, so we try for a more reasonable.
Equation (8) specifically calculate how much you can borrow using $600 for principal and interest. This number is usually arrived at by trail and error.
Equation (9-10) is one of two possible ways of comparing the 15 and 30 year mortgage taking into account that they are at different interest rate. In equation (9) I assume I make the assumption that I take the 30 year note, but faithfully make the calculated 15 year payment. Because of the higher interest rate, I still have a balance of $15,264 at the end of 15 years. In Equation (10) I alternatively calculate how much longer I would have to pay to eliminate the mortgage. The answer is one year with a small balance remaining.
The third way to evaluate the penalty for taking the 30 year mortgage and trying to pay it off in 15 years, is that the required payment would be $1,312.06, which is higher than the $1,251.04 payment if you took the mortgage with the lower interest rate that legally requires you to pay it off in 15 years.
The last calculation are for people who want to pay off a mortgage in 15 years, but they don't want to risk default if they can't make the payments.
I didn't attach the spreadsheet, because the functions are fairly straightforward. You just fill them with the known variables in the positions required.
Once you have the equations you can change the inputs to account for knowing things like "how much you would owe after ten years" or some other value of interest. For a real mortgage, the bank will give you a payment schedule, but it you might want to run through some hypothetical scenarios before signing up.
Most people are familiar with the PMT (short for Payment) function in Excel or with a financial calculator. Really old school is to use a look up table with factors that you multiply by the amount you are borrowing. (30 year note at 4% has look up factor is .00477415 or $477.42 per month for every $100,000 you borrow). Then all that is required is an old fashioned calculator or hand multiplication.
Such tables are usually calculated for specific time periods, and for a range of interest rates that are considered meaningful at the time.
Table posted on The Mortgage Insider
Not everyone realizes that Excel has functions for all 5 unknown variables in the formula. It is possible to calculate each variable, which may save you time and effort hunting around.
| # | NPY | YRS | PMT | PV | RATE | FV | Unknown |
|---|---|---|---|---|---|---|---|
| 1 | 12 | 30 | -$855.78 | $175,000 | 4.200% | $0 | PMT |
| 2 | 12 | 15 | -$1,251.04 | $175,000 | 3.500% | $0 | PMT |
| 3 biweekly | 26 | 30 | -$394.79 | $175,000 | 4.200% | $0 | PMT |
| 4 remaining balance | 12 | 15 | -$855.78 | $175,000 | 4.200% | -$114,142 | FV |
| 5 degenerate | 12 | 15 | -$395.26 | $0 | 0.000% | $71,148 | FV |
| 6 | 12 | 15 | -$395.26 | $128,473 | 7.177% | $0 | RATE |
| 6 alternative | 12 | 15 | -$395.26 | $0 | -7.177% | $128,473 | RATE |
| 7 | 12 | 15 | -$395.26 | $100,000 | 4.279% | $0 | RATE |
| 8 | 12 | 30 | -$600.00 | $122,695 | 4.200% | $0 | PV |
| 9 | 12 | 15 | -$1,251.04 | $175,000 | 4.200% | -$15,264 | FV |
| 10 | 12 | 16.04 | -$1,251.04 | $175,000 | 4.200% | $0 | NPER |
Equations (1-3) are standard PMT calculations. One each for a 15 year, 30 year monthly, and 30 year biweekly. Future Value is set to zero, as most people calculate payments to eliminate a loan in a certain time. You can input a variable if you are trying to reduce it to a certain amount in a given time period. PMT is calculated as a negative number to indicate the financial convention that it is an expense.
Equation (4) is a common use of Future Value (FV). You have a loan, and you want to know the remaining principal after a certain time period. In this case I used the inputs from Equation (1) to see what I owed halfway through the mortgage. FV defaults to a negative number since it is money you owe.
Equation (5) is a degenerate case. I am just showing that the equations should work with otherwise simple solutions. The money put away in 15 years would be 15*12*PMT, but the formula produces the same result. This is the basis for your capital gains tax.
Equation (6) calculates the interest rate to get a PV required to pay a 25% capital gains tax and still have enough to pay off the mortgage using just the difference between the 15 year and 30 year payment
Equation (7) calculates the interest rate to get a PV=$100K. The rate calculated in equation #6 may be unrealistic, so we try for a more reasonable.
Equation (8) specifically calculate how much you can borrow using $600 for principal and interest. This number is usually arrived at by trail and error.
Equation (9-10) is one of two possible ways of comparing the 15 and 30 year mortgage taking into account that they are at different interest rate. In equation (9) I assume I make the assumption that I take the 30 year note, but faithfully make the calculated 15 year payment. Because of the higher interest rate, I still have a balance of $15,264 at the end of 15 years. In Equation (10) I alternatively calculate how much longer I would have to pay to eliminate the mortgage. The answer is one year with a small balance remaining.
The third way to evaluate the penalty for taking the 30 year mortgage and trying to pay it off in 15 years, is that the required payment would be $1,312.06, which is higher than the $1,251.04 payment if you took the mortgage with the lower interest rate that legally requires you to pay it off in 15 years.
The last calculation are for people who want to pay off a mortgage in 15 years, but they don't want to risk default if they can't make the payments.
I didn't attach the spreadsheet, because the functions are fairly straightforward. You just fill them with the known variables in the positions required.
Once you have the equations you can change the inputs to account for knowing things like "how much you would owe after ten years" or some other value of interest. For a real mortgage, the bank will give you a payment schedule, but it you might want to run through some hypothetical scenarios before signing up.
June 6th, 2013 at 6:42:52 PM
permalink
Very interesting read indeed however the 5 TVM financial functions in Excel or for that matter on financial calculators are rather of little use if at all.
The reason for limitation of RATE, NPER, PMT, PV & FV is attributed to the underlying TVM equation upon which these functions are programmed
Take for example the following TVM equation
PV (1+RATE)^NPER + PMT (1+RATE*type) [ (1+RATE)^NPER -1 ] / RATE + FV = 0
Using this equation one can solve for all four variable excepting RATE which can not be solved using algebraic manipulation and one has to resort to numerical methods to find one or more interest rates
But the point about this equation that hinders serious time value of money calculations is the fact that this equation only allows for periodic payments in constant amount
Yet in finance, banking and accounting one comes across annuity payments that either grow or shrink by a rate or payments that increase or decrease by an amount
Thus none of the 5 variables can be solved for when there is a GRADIENT involved since the equation has not catered for such cases
And to make things more interesting, how about have payments that never end or continuous streams of payments. How would you find present value, the annuity payment and the interest rate for such a perpetuity
Then there is the question about compounding frequency of interest and here Excel or financial calculators only permit periodic compounding of interest without permitting intra-year compounding or even infinite compounding of interest
To add more to the pain, the period represented by NPER is generic without any way of telling Excel that our period is a year, or quarter or month or day or hour or minute or even biennial
Microsoft has a long way to go to address such issues with its outdated and useless financial functions not just the 5 TVM functions
The reason for limitation of RATE, NPER, PMT, PV & FV is attributed to the underlying TVM equation upon which these functions are programmed
Take for example the following TVM equation
PV (1+RATE)^NPER + PMT (1+RATE*type) [ (1+RATE)^NPER -1 ] / RATE + FV = 0
Using this equation one can solve for all four variable excepting RATE which can not be solved using algebraic manipulation and one has to resort to numerical methods to find one or more interest rates
But the point about this equation that hinders serious time value of money calculations is the fact that this equation only allows for periodic payments in constant amount
Yet in finance, banking and accounting one comes across annuity payments that either grow or shrink by a rate or payments that increase or decrease by an amount
Thus none of the 5 variables can be solved for when there is a GRADIENT involved since the equation has not catered for such cases
And to make things more interesting, how about have payments that never end or continuous streams of payments. How would you find present value, the annuity payment and the interest rate for such a perpetuity
Then there is the question about compounding frequency of interest and here Excel or financial calculators only permit periodic compounding of interest without permitting intra-year compounding or even infinite compounding of interest
To add more to the pain, the period represented by NPER is generic without any way of telling Excel that our period is a year, or quarter or month or day or hour or minute or even biennial
Microsoft has a long way to go to address such issues with its outdated and useless financial functions not just the 5 TVM functions
June 7th, 2013 at 10:40:30 PM
permalink
A couple of example where the GRADIENT comes in to play. Say you have deposited an initial amount of $1000 in a savings account that pays an annual rate of 5% compounded monthly. Say now you make a deposit of $100 at the end of first month and the payments from month 2 to 12 increase by 10%. Thus the second payment at the end of second month would be in amount of $110. This will continue in such pattern till the end of next 12 months.
You would be interested in knowing how much of money your account has at the end of the year. Excel has a FV function but it only permits annuity payments in constant amount yet here our payments start at $100 and grow by 10% each month.
Another scenario would be if the payments were to increase by a constant money amount each month say you made the first deposit in amount of $100 but then the next payments is increased by $5 making it in amount of $105, and the one that follows will be in amount of $110 and so on.
Here once again Excel FV has no built in capability to get us the results.
Thus far the gradient has coincided with the payment schedule, but it may be that gradient occurs at a schedule different than that of payment. In real life cases an increase occurs annually such as an annual increment in salary. So if you were saving in an IRA with amounts of $100 each month but such payments increased by 10% each year. Now the first 12 payment will be in amount of $100 and the next 12 payments will be in amount of $110.
I was reading though another thread where someone had posted links to mortgage calculators using real money amounts. I didn't look at those calculators but it be a mighty difficult task to predict an inflation rate in the future so really unsure how he has come up with the numbers for real money amounts.
But if you were to be finding the present value of loan, or mortgage payment when there is an element of inflation the Excel PV and PMT function can be of no help.
All the examples I listed in previous paragraphs can be easily solved using the 6 time value of money functions offered by 3rd party Excel add-in called tadXL
This add has it's own set of TVM functions that allow for more values to be passed to them thus enabling you to find present and future value of growing, shrinking, increasing and decreasing annuities. The new function for GRADIENT finds the growth rate or growth amount when annuity payments grow, shrink, increase or decrease.
You would be interested in knowing how much of money your account has at the end of the year. Excel has a FV function but it only permits annuity payments in constant amount yet here our payments start at $100 and grow by 10% each month.
Another scenario would be if the payments were to increase by a constant money amount each month say you made the first deposit in amount of $100 but then the next payments is increased by $5 making it in amount of $105, and the one that follows will be in amount of $110 and so on.
Here once again Excel FV has no built in capability to get us the results.
Thus far the gradient has coincided with the payment schedule, but it may be that gradient occurs at a schedule different than that of payment. In real life cases an increase occurs annually such as an annual increment in salary. So if you were saving in an IRA with amounts of $100 each month but such payments increased by 10% each year. Now the first 12 payment will be in amount of $100 and the next 12 payments will be in amount of $110.
I was reading though another thread where someone had posted links to mortgage calculators using real money amounts. I didn't look at those calculators but it be a mighty difficult task to predict an inflation rate in the future so really unsure how he has come up with the numbers for real money amounts.
But if you were to be finding the present value of loan, or mortgage payment when there is an element of inflation the Excel PV and PMT function can be of no help.
All the examples I listed in previous paragraphs can be easily solved using the 6 time value of money functions offered by 3rd party Excel add-in called tadXL
This add has it's own set of TVM functions that allow for more values to be passed to them thus enabling you to find present and future value of growing, shrinking, increasing and decreasing annuities. The new function for GRADIENT finds the growth rate or growth amount when annuity payments grow, shrink, increase or decrease.
June 10th, 2013 at 7:19:01 PM
permalink
Quote: FinEngineerVery interesting read indeed however the 5 TVM financial functions in Excel or for that matter on financial calculators are rather of little use if at all.
You know I still can' figure out all the non-gradient calculations. For instance look at this ADVERTISEMENT:
Adjustable-Rate Mortgage: The initial payment on a 30-year $200,000 5-year Adjustable-Rate Loan at 2.50% and 70% loan-to-value (LTV) is $790.25 with 1.625 points due at closing. The Annual Percentage Rate (APR) is 3.022%. After the initial 5 years, the principal and interest payment is $835.33. The fully indexed rate of 3.00% is in effect for the remaining 25 years and can change once every year for the remaining life of the loan. Payment does not include taxes and insurance premiums. The actual payment amount will be greater. Rate is variable and subject to change after 5 years.
$200k @ 2.50% COMPOUNDED MONTHLY for 30 years, PMT IS -$790.24
After 5 years principal is $176,150.76 @ 3.00% COMPOUNDED MONTHLY for 25 years, PMT IS -$835.33
But I am not sure how you get 3.022% ?
June 12th, 2013 at 6:27:43 AM
permalink
Quote: pacomartinBut I am not sure how you get 3.022% ?
You have just given me ideas for the next version of tadXL v3.0!
Ok, I haven't been dealing with math related to loans and mortgages but I had a look around and I think I found the lender from whom you quoted that advertisement
When I looked up at their site for information specific to APR for ARM it briefly hinted that it is the IRR of the cash flows. And that fees, points, PMI etc are included in calculation of APR
But it seems a bit reversed when their pages state that usually an APR for an ARM would turn out to be lower than the fully indexed rate however in the example you quoted and the example on that same site shows an APR that is higher than the fully indexed rate
Since in this and the example on the lenders site mentioned points inclusion at closing thus the initial cash out flow for this loan would be 196750 = 200,000 - 3250
Now thinking that an APR would now simply be the IRR for the cash flows for the lender where the payments from month 1 to 60 are in amount of $790.25 and payments from months 61 to 360 are in amounts of $835.33
Finding the IRR would be either using the series of all 361 money amounts or the alternative would be more like Excel RATE calculation this time though there will be two different payments rather than 1 as is usually the case
I set up my own Excel RATE calculator with the help of extended TVM equation and found the APR which it seems is different from the one quoted in the ad as 3.022%.
May be there is more to this APR calculation that I can think of but then the places I looked around on the web too produced different APR for the same numbers
--------------------------
guess = 10%
nper1 = 60
nper2 = 360
pmt1 = -790.241797635
pmt2 = -835.326845070
pv = 196750
fv = 0
type = 0
FV + PMT1 fvifa(i%, NPER1) + PMT2 fvifa(i%, NPER2) - PMT2 fvifa(i%, NPER1) + PV fvif(i%, NPER2) = 0
f(i) = 0 + -790.241797635 fvifa(i%, 60) + -835.326845070 fvifa(i%, 360) - -835.326845070 fvifa(i%, 60) + 196750 fvif(i%, 360)
f'(i) = 0 + -790.241797635 fvifa'(i%, 60) + -835.326845070 fvifa'(i%, 360) - -835.326845070 fvifa'(i%, 60) + 196750 fvif'(i%, 360)
i0 = 0.1
f(i1) = 1.50120506492E+20
f'(i1) = 4.91969090784E+22
i1 = 0.1 - 1.50120506492E+20/4.91969090784E+22 = 0.0969485784919
Error Bound = 0.0969485784919 - 0.1 = 0.003051 > 0.000001
i1 = 0.0969485784919
f(i2) = 5.51472615229E+19
f'(i2) = 1.81244513698E+22
i2 = 0.0969485784919 - 5.51472615229E+19/1.81244513698E+22 = 0.0939058788594
Error Bound = 0.0939058788594 - 0.0969485784919 = 0.003043 > 0.000001
i2 = 0.0939058788594
f(i3) = 2.02584491627E+19
f'(i3) = 6.67718907088E+21
i3 = 0.0939058788594 - 2.02584491627E+19/6.67718907088E+21 = 0.0908719001975
Error Bound = 0.0908719001975 - 0.0939058788594 = 0.003034 > 0.000001
i3 = 0.0908719001975
f(i4) = 7.44194627579E+18
f'(i4) = 2.45993973481E+21
i4 = 0.0908719001975 - 7.44194627579E+18/2.45993973481E+21 = 0.0878466446717
Error Bound = 0.0878466446717 - 0.0908719001975 = 0.003025 > 0.000001
i4 = 0.0878466446717
f(i5) = 2.73378711502E+18
f'(i5) = 9.06269840001E+20
i5 = 0.0878466446717 - 2.73378711502E+18/9.06269840001E+20 = 0.0848301180321
Error Bound = 0.0848301180321 - 0.0878466446717 = 0.003017 > 0.000001
i5 = 0.0848301180321
f(i6) = 1.0042466043E+18
f'(i6) = 3.33882132804E+20
i6 = 0.0848301180321 - 1.0042466043E+18/3.33882132804E+20 = 0.0818223302363
Error Bound = 0.0818223302363 - 0.0848301180321 = 0.003008 > 0.000001
i6 = 0.0818223302363
f(i7) = 3.68903863307E+17
f'(i7) = 1.23007561772E+20
i7 = 0.0818223302363 - 3.68903863307E+17/1.23007561772E+20 = 0.0788232962095
Error Bound = 0.0788232962095 - 0.0818223302363 = 0.002999 > 0.000001
i7 = 0.0788232962095
f(i8) = 1.35513558813E+17
f'(i8) = 4.53183283722E+19
i8 = 0.0788232962095 - 1.35513558813E+17/4.53183283722E+19 = 0.0758330367785
Error Bound = 0.0758330367785 - 0.0788232962095 = 0.00299 > 0.000001
i8 = 0.0758330367785
f(i9) = 4.97792663791E+16
f'(i9) = 1.66962888223E+19
i9 = 0.0758330367785 - 4.97792663791E+16/1.66962888223E+19 = 0.0728515798266
Error Bound = 0.0728515798266 - 0.0758330367785 = 0.002981 > 0.000001
i9 = 0.0728515798266
f(i10) = 1.82856245035E+16
f'(i10) = 6.15135343161E+18
i10 = 0.0728515798266 - 1.82856245035E+16/6.15135343161E+18 = 0.0698789617342
Error Bound = 0.0698789617342 - 0.0728515798266 = 0.002973 > 0.000001
i10 = 0.0698789617342
f(i11) = 6.71685390042E+15
f'(i11) = 2.26634954342E+18
i11 = 0.0698789617342 - 6.71685390042E+15/2.26634954342E+18 = 0.0669152291891
Error Bound = 0.0669152291891 - 0.0698789617342 = 0.002964 > 0.000001
i11 = 0.0669152291891
f(i12) = 2.46726540858E+15
f'(i12) = 8.35005981683E+17
i12 = 0.0669152291891 - 2.46726540858E+15/8.35005981683E+17 = 0.0639604414837
Error Bound = 0.0639604414837 - 0.0669152291891 = 0.002955 > 0.000001
i12 = 0.0639604414837
f(i13) = 9.0627196482E+14
f'(i13) = 3.07652183316E+17
i13 = 0.0639604414837 - 9.0627196482E+14/3.07652183316E+17 = 0.0610146734575
Error Bound = 0.0610146734575 - 0.0639604414837 = 0.002946 > 0.000001
i13 = 0.0610146734575
f(i14) = 3.3288366867E+14
f'(i14) = 1.13354740265E+17
i14 = 0.0610146734575 - 3.3288366867E+14/1.13354740265E+17 = 0.0580780193053
Error Bound = 0.0580780193053 - 0.0610146734575 = 0.002937 > 0.000001
i14 = 0.0580780193053
f(i15) = 1.22268867535E+14
f'(i15) = 4.17667416485E+16
i15 = 0.0580780193053 - 1.22268867535E+14/4.17667416485E+16 = 0.0551505975647
Error Bound = 0.0551505975647 - 0.0580780193053 = 0.002927 > 0.000001
i15 = 0.0551505975647
f(i16) = 4.49082822812E+13
f'(i16) = 1.53898797777E+16
i16 = 0.0551505975647 - 4.49082822812E+13/1.53898797777E+16 = 0.0522325577275
Error Bound = 0.0522325577275 - 0.0551505975647 = 0.002918 > 0.000001
i16 = 0.0522325577275
f(i17) = 1.64938175562E+13
f'(i17) = 5.6709629072E+15
i17 = 0.0522325577275 - 1.64938175562E+13/5.6709629072E+15 = 0.049324089126
Error Bound = 0.049324089126 - 0.0522325577275 = 0.002908 > 0.000001
i17 = 0.049324089126
f(i18) = 6.05753943838E+12
f'(i18) = 2.08977515561E+15
i18 = 0.049324089126 - 6.05753943838E+12/2.08977515561E+15 = 0.0464254330566
Error Bound = 0.0464254330566 - 0.049324089126 = 0.002899 > 0.000001
i18 = 0.0464254330566
f(i19) = 2.22457226471E+12
f'(i19) = 7.7013899814E+14
i19 = 0.0464254330566 - 2.22457226471E+12/7.7013899814E+14 = 0.043536899597
Error Bound = 0.043536899597 - 0.0464254330566 = 0.002889 > 0.000001
i19 = 0.043536899597
f(i20) = 816892872745
f'(i20) = 2.83839657838E+14
i20 = 0.043536899597 - 816892872745/2.83839657838E+14 = 0.0406588913617
Error Bound = 0.0406588913617 - 0.043536899597 = 0.002878 > 0.000001
i20 = 0.0406588913617
f(i21) = 299945664326
f'(i21) = 1.04621736116E+14
i21 = 0.0406588913617 - 299945664326/1.04621736116E+14 = 0.037791937749
Error Bound = 0.037791937749 - 0.0406588913617 = 0.002867 > 0.000001
i21 = 0.037791937749
f(i22) = 110119894058
f'(i22) = 3.85682930438E+13
i22 = 0.037791937749 - 110119894058/3.85682930438E+13 = 0.03493674543
Error Bound = 0.03493674543 - 0.037791937749 = 0.002855 > 0.000001
i22 = 0.03493674543
f(i23) = 40421818371
f'(i23) = 1.42206628266E+13
i23 = 0.03493674543 - 40421818371/1.42206628266E+13 = 0.032094274663
Error Bound = 0.032094274663 - 0.03493674543 = 0.002842 > 0.000001
i23 = 0.032094274663
f(i24) = 14834233297.2
f'(i24) = 5.24471261136E+12
i24 = 0.032094274663 - 14834233297.2/5.24471261136E+12 = 0.0292658578562
Error Bound = 0.0292658578562 - 0.032094274663 = 0.002828 > 0.000001
i24 = 0.0292658578562
f(i25) = 5442165478.31
f'(i25) = 1.93501386854E+12
i25 = 0.0292658578562 - 5442165478.31/1.93501386854E+12 = 0.0264533893944
Error Bound = 0.0264533893944 - 0.0292658578562 = 0.002812 > 0.000001
i25 = 0.0264533893944
f(i26) = 1995585979.22
f'(i26) = 714303763069
i26 = 0.0264533893944 - 1995585979.22/714303763069 = 0.0236596396168
Error Bound = 0.0236596396168 - 0.0264533893944 = 0.002794 > 0.000001
i26 = 0.0236596396168
f(i27) = 731232290.137
f'(i27) = 263902052212
i27 = 0.0236596396168 - 731232290.137/263902052212 = 0.0208887923119
Error Bound = 0.0208887923119 - 0.0236596396168 = 0.002771 > 0.000001
i27 = 0.0208887923119
f(i28) = 267638035.228
f'(i28) = 97628428527.5
i28 = 0.0208887923119 - 267638035.228/97628428527.5 = 0.0181473978301
Error Bound = 0.0181473978301 - 0.0208887923119 = 0.002741 > 0.000001
i28 = 0.0181473978301
f(i29) = 97776056.6605
f'(i29) = 36196248890.5
i29 = 0.0181473978301 - 97776056.6605/36196248890.5 = 0.0154461218787
Error Bound = 0.0154461218787 - 0.0181473978301 = 0.002701 > 0.000001
i29 = 0.0154461218787
f(i30) = 35606162.8466
f'(i30) = 13471512229.6
i30 = 0.0154461218787 - 35606162.8466/13471512229.6 = 0.0128030509123
Error Bound = 0.0128030509123 - 0.0154461218787 = 0.002643 > 0.000001
i30 = 0.0128030509123
f(i31) = 12890951.2062
f'(i31) = 5049344880.79
i31 = 0.0128030509123 - 12890951.2062/5049344880.79 = 0.0102500561159
Error Bound = 0.0102500561159 - 0.0128030509123 = 0.002553 > 0.000001
i31 = 0.0102500561159
f(i32) = 4614790.3308
f'(i32) = 1918739712.5
i32 = 0.0102500561159 - 4614790.3308/1918739712.5 = 0.00784494076817
Error Bound = 0.00784494076817 - 0.0102500561159 = 0.002405 > 0.000001
i32 = 0.00784494076817
f(i33) = 1614043.2013
f'(i33) = 749966260.167
i33 = 0.00784494076817 - 1614043.2013/749966260.167 = 0.00569278634864
Error Bound = 0.00569278634864 - 0.00784494076817 = 0.002152 > 0.000001
i33 = 0.00569278634864
f(i34) = 535983.4717
f'(i34) = 311452699.305
i34 = 0.00569278634864 - 535983.4717/311452699.305 = 0.00397187183118
Error Bound = 0.00397187183118 - 0.00569278634864 = 0.001721 > 0.000001
i34 = 0.00397187183118
f(i35) = 156860.0801
f'(i35) = 147492751.253
i35 = 0.00397187183118 - 156860.0801/147492751.253 = 0.00290836139568
Error Bound = 0.00290836139568 - 0.00397187183118 = 0.001064 > 0.000001
i35 = 0.00290836139568
f(i36) = 32810.3914
f'(i36) = 89892698.9612
i36 = 0.00290836139568 - 32810.3914/89892698.9612 = 0.00254336633135
Error Bound = 0.00254336633135 - 0.00290836139568 = 0.000365 > 0.000001
i36 = 0.00254336633135
f(i37) = 2748.6833
f'(i37) = 75197254.8663
i37 = 0.00254336633135 - 2748.6833/75197254.8663 = 0.00250681335717
Error Bound = 0.00250681335717 - 0.00254336633135 = 3.7E-5 > 0.000001
i37 = 0.00250681335717
f(i38) = 24.8094
f'(i38) = 73843200.0827
i38 = 0.00250681335717 - 24.8094/73843200.0827 = 0.00250647738268
Error Bound = 0.00250647738268 - 0.00250681335717 = 0 < 0.000001
IRR = 0.2506477%
Annual IRR = 3.0077729%
APR = 3.0495853%
June 12th, 2013 at 8:46:19 AM
permalink
Quote: FinEngineer
FV + PMT1 fvifa(i%, NPER1) + PMT2 fvifa(i%, NPER2) - PMT2 fvifa(i%, NPER1) + PV fvif(i%, NPER2) = 0
f(i) = 0 + -790.241797635 fvifa(i%, 60) + -835.326845070 fvifa(i%, 360) - -835.326845070 fvifa(i%, 60) + 196750 fvif(i%, 360)
f'(i) = 0 + -790.241797635 fvifa'(i%, 60) + -835.326845070 fvifa'(i%, 360) - -835.326845070 fvifa'(i%, 60) + 196750 fvif'(i%,360)
i0 = 0.1
f(i1) = 1.50120506492E+20
f'(i1) = 4.91969090784E+22
i1 = 0.1 - 1.50120506492E+20/4.91969090784E+22 = 0.0969485784919
Error Bound = 0.0969485784919 - 0.1 = 0.003051 > 0.000001
i1 = 0.0969485784919
f(i2) = 5.51472615229E+19
f'(i2) = 1.81244513698E+22
i2 = 0.0969485784919 - 5.51472615229E+19/1.81244513698E+22 = 0.0939058788594
Error Bound = 0.0939058788594 - 0.0969485784919 = 0.003043 > 0.000001
IRR = 0.2506477%
Annual IRR = 3.0077729%
APR = 3.0495853%
OK, it would seem that the use of extended TVM equation when cash flows are compounded are generating erroneous IRR values
This is most likely as you can see from the initial iterations that the values for function f(i) and it's derivative f'(i) are overflowing the allowed limits for such variables
And when I checked the net future value of the cash flows using the IRR value of 0.00250647735483 it did not result in zero so there is definitely an overflow error in the calculations
However I altered the extended TVM equation that uses the discounted values of cash flows and hence no chance of overflowing the limit of variables
And this time the IRR comes out as expected at which the net present value of the cash flows is zero
--------------------------
guess = 3%
nper1 = 60
nper2 = 360
pmt1 = -790.241797635
pmt2 = -835.326845070
pv = 196750
fv = 0
type = 0
PV + PMT1 pvifa(i%, NPER1) + PMT2 pvifa(i%, NPER2) - PMT2 pvifa(i%, NPER1) + FV pvif(i%, NPER2) = 0
f(i) = 196750 + -790.241797635 pvifa(i%, 60) + -835.326845070 pvifa(i%, 360) - -835.326845070 pvifa(i%, 60) + 0 pvif(i%, 360)
f'(i) = 0 + -790.241797635 pvifa'(i%, 60) + -835.326845070 pvifa'(i%, 360) - -835.326845070 pvifa'(i%, 60) + 0 pvif'(i%, 360)
i0 = 0.03
f(i1) = 170154.1917
f'(i1) = 901153.3176
i1 = 0.03 - 170154.1917/901153.3176 = -0.158818249232
Error Bound = -0.158818249232 - 0.03 = 0.188818 > 0.000001
i1 = -0.158818249232
f(i2) = -5.76252524493E+30
f'(i2) = 2.42990036211E+33
i2 = -0.158818249232 - -5.76252524493E+30/2.42990036211E+33 = -0.156446742427
Error Bound = -0.156446742427 - -0.158818249232 = 0.002372 > 0.000001
i2 = -0.156446742427
f(i3) = -2.12318302727E+30
f'(i3) = 8.92531423489E+32
i3 = -0.156446742427 - -2.12318302727E+30/8.92531423489E+32 = -0.154067909624
Error Bound = -0.154067909624 - -0.156446742427 = 0.002379 > 0.000001
i3 = -0.154067909624
f(i4) = -7.82283509188E+29
f'(i4) = 3.27835799078E+32
i4 = -0.154067909624 - -7.82283509188E+29/3.27835799078E+32 = -0.151681704361
Error Bound = -0.151681704361 - -0.154067909624 = 0.002386 > 0.000001
i4 = -0.151681704361
f(i5) = -2.8823265024E+29
f'(i5) = 1.20416759386E+32
i5 = -0.151681704361 - -2.8823265024E+29/1.20416759386E+32 = -0.149288078659
Error Bound = -0.149288078659 - -0.151681704361 = 0.002394 > 0.000001
i5 = -0.149288078659
f(i6) = -1.06200008207E+29
f'(i6) = 4.42298099635E+31
i6 = -0.149288078659 - -1.06200008207E+29/4.42298099635E+31 = -0.146886982921
Error Bound = -0.146886982921 - -0.149288078659 = 0.002401 > 0.000001
i6 = -0.146886982921
f(i7) = -3.91298719642E+28
f'(i7) = 1.62457834848E+31
i7 = -0.146886982921 - -3.91298719642E+28/1.62457834848E+31 = -0.144478365817
Error Bound = -0.144478365817 - -0.146886982921 = 0.002409 > 0.000001
i7 = -0.144478365817
f(i8) = -1.44176653068E+28
f'(i8) = 5.96710334469E+30
i8 = -0.144478365817 - -1.44176653068E+28/5.96710334469E+30 = -0.142062174162
Error Bound = -0.142062174162 - -0.144478365817 = 0.002416 > 0.000001
i8 = -0.142062174162
f(i9) = -5.31232006619E+27
f'(i9) = 2.19171268074E+30
i9 = -0.142062174162 - -5.31232006619E+27/2.19171268074E+30 = -0.139638352777
Error Bound = -0.139638352777 - -0.142062174162 = 0.002424 > 0.000001
i9 = -0.139638352777
f(i10) = -1.9573859383E+27
f'(i10) = 8.05008902715E+29
i10 = -0.139638352777 - -1.9573859383E+27/8.05008902715E+29 = -0.13720684434
Error Bound = -0.13720684434 - -0.139638352777 = 0.002432 > 0.000001
i10 = -0.13720684434
f(i11) = -7.21226706302E+26
f'(i11) = 2.95674979695E+29
i11 = -0.13720684434 - -7.21226706302E+26/2.95674979695E+29 = -0.134767589226
Error Bound = -0.134767589226 - -0.13720684434 = 0.002439 > 0.000001
i11 = -0.134767589226
f(i12) = -2.65748255293E+26
f'(i12) = 1.08598821136E+29
i12 = -0.134767589226 - -2.65748255293E+26/1.08598821136E+29 = -0.132320525322
Error Bound = -0.132320525322 - -0.134767589226 = 0.002447 > 0.000001
i12 = -0.132320525322
f(i13) = -9.79202528711E+25
f'(i13) = 3.98870656038E+28
i13 = -0.132320525322 - -9.79202528711E+25/3.98870656038E+28 = -0.129865587828
Error Bound = -0.129865587828 - -0.132320525322 = 0.002455 > 0.000001
i13 = -0.129865587828
f(i14) = -3.60809800119E+25
f'(i14) = 1.46499211421E+28
i14 = -0.129865587828 - -3.60809800119E+25/1.46499211421E+28 = -0.127402709041
Error Bound = -0.127402709041 - -0.129865587828 = 0.002463 > 0.000001
i14 = -0.127402709041
f(i15) = -1.32949910171E+25
f'(i15) = 5.38064664809E+27
i15 = -0.127402709041 - -1.32949910171E+25/5.38064664809E+27 = -0.124931818108
Error Bound = -0.124931818108 - -0.127402709041 = 0.002471 > 0.000001
i15 = -0.124931818108
f(i16) = -4.89893802681E+24
f'(i16) = 1.976193136E+27
i16 = -0.124931818108 - -4.89893802681E+24/1.976193136E+27 = -0.122452840756
Error Bound = -0.122452840756 - -0.124931818108 = 0.002479 > 0.000001
i16 = -0.122452840756
f(i17) = -1.8051790671E+24
f'(i17) = 7.25804654088E+26
i17 = -0.122452840756 - -1.8051790671E+24/7.25804654088E+26 = -0.119965698993
Error Bound = -0.119965698993 - -0.122452840756 = 0.002487 > 0.000001
i17 = -0.119965698993
f(i18) = -6.65186419988E+23
f'(i18) = 2.66566305704E+26
i18 = -0.119965698993 - -6.65186419988E+23/2.66566305704E+26 = -0.117470310769
Error Bound = -0.117470310769 - -0.119965698993 = 0.002495 > 0.000001
i18 = -0.117470310769
f(i19) = -2.4511592387E+23
f'(i19) = 9.79006478389E+25
i19 = -0.117470310769 - -2.4511592387E+23/9.79006478389E+25 = -0.114966589606
Error Bound = -0.114966589606 - -0.117470310769 = 0.002504 > 0.000001
i19 = -0.114966589606
f(i20) = -9.03244026118E+22
f'(i20) = 3.59550850034E+25
i20 = -0.114966589606 - -9.03244026118E+22/3.59550850034E+25 = -0.112454444173
Error Bound = -0.112454444173 - -0.114966589606 = 0.002512 > 0.000001
i20 = -0.112454444173
f(i21) = -3.32846871245E+22
f'(i21) = 1.32047174746E+25
i21 = -0.112454444173 - -3.32846871245E+22/1.32047174746E+25 = -0.109933777813
Error Bound = -0.109933777813 - -0.112454444173 = 0.002521 > 0.000001
i21 = -0.109933777813
f(i22) = -1.22656366847E+22
f'(i22) = 4.84943903992E+24
i22 = -0.109933777813 - -1.22656366847E+22/4.84943903992E+24 = -0.107404488016
Error Bound = -0.107404488016 - -0.109933777813 = 0.002529 > 0.000001
i22 = -0.107404488016
f(i23) = -4.52004149365E+21
f'(i23) = 1.78093063325E+24
i23 = -0.107404488016 - -4.52004149365E+21/1.78093063325E+24 = -0.104866465811
Error Bound = -0.104866465811 - -0.107404488016 = 0.002538 > 0.000001
i23 = -0.104866465811
f(i24) = -1.66571990269E+21
f'(i24) = 6.54026089562E+23
i24 = -0.104866465811 - -1.66571990269E+21/6.54026089562E+23 = -0.102319595083
Error Bound = -0.102319595083 - -0.104866465811 = 0.002547 > 0.000001
i24 = -0.102319595083
f(i25) = -6.13859997145E+20
f'(i25) = 2.40179044201E+23
i25 = -0.102319595083 - -6.13859997145E+20/2.40179044201E+23 = -0.0997637517986
Error Bound = -0.0997637517986 - -0.102319595083 = 0.002556 > 0.000001
i25 = -0.0997637517986
f(i26) = -2.26227364847E+20
f'(i26) = 8.81995675943E+22
i26 = -0.0997637517986 - -2.26227364847E+20/8.81995675943E+22 = -0.0971988031144
Error Bound = -0.0971988031144 - -0.0997637517986 = 0.002565 > 0.000001
i26 = -0.0971988031144
f(i27) = -8.33738967451E+19
f'(i27) = 3.23883155276E+22
i27 = -0.0971988031144 - -8.33738967451E+19/3.23883155276E+22 = -0.094624606359
Error Bound = -0.094624606359 - -0.0971988031144 = 0.002574 > 0.000001
i27 = -0.094624606359
f(i28) = -3.0727343909E+19
f'(i28) = 1.18932349292E+22
i28 = -0.094624606359 - -3.0727343909E+19/1.18932349292E+22 = -0.0920410078602
Error Bound = -0.0920410078602 - -0.094624606359 = 0.002584 > 0.000001
i28 = -0.0920410078602
f(i29) = -1.13248066284E+19
f'(i29) = 4.36717334914E+21
i29 = -0.0920410078602 - -1.13248066284E+19/4.36717334914E+21 = -0.0894478415892
Error Bound = -0.0894478415892 - -0.0920410078602 = 0.002593 > 0.000001
i29 = -0.0894478415892
f(i30) = -4.17396140972E+18
f'(i30) = 1.60357253636E+21
i30 = -0.0894478415892 - -4.17396140972E+18/1.60357253636E+21 = -0.0868449275862
Error Bound = -0.0868449275862 - -0.0894478415892 = 0.002603 > 0.000001
i30 = -0.0868449275862
f(i31) = -1.5384346837E+18
f'(i31) = 5.88793956577E+20
i31 = -0.0868449275862 - -1.5384346837E+18/5.88793956577E+20 = -0.0842320701231
Error Bound = -0.0842320701231 - -0.0868449275862 = 0.002613 > 0.000001
i31 = -0.0842320701231
f(i32) = -5.6705343426E+17
f'(i32) = 2.16183867171E+20
i32 = -0.0842320701231 - -5.6705343426E+17/2.16183867171E+20 = -0.0816090555491
Error Bound = -0.0816090555491 - -0.0842320701231 = 0.002623 > 0.000001
i32 = -0.0816090555491
f(i33) = -2.09018471712E+17
f'(i33) = 7.93719189857E+19
i33 = -0.0816090555491 - -2.09018471712E+17/7.93719189857E+19 = -0.0789756497503
Error Bound = -0.0789756497503 - -0.0816090555491 = 0.002633 > 0.000001
i33 = -0.0789756497503
f(i34) = -7.70482527126E+16
f'(i34) = 2.91401895892E+19
i34 = -0.0789756497503 - -7.70482527126E+16/2.91401895892E+19 = -0.0763315951372
Error Bound = -0.0763315951372 - -0.0789756497503 = 0.002644 > 0.000001
i34 = -0.0763315951372
f(i35) = -2.84027440056E+16
f'(i35) = 1.06978800093E+19
i35 = -0.0763315951372 - -2.84027440056E+16/1.06978800093E+19 = -0.0736766070478
Error Bound = -0.0736766070478 - -0.0763315951372 = 0.002655 > 0.000001
i35 = -0.0736766070478
f(i36) = -1.04707893164E+16
f'(i36) = 3.92717784794E+18
i36 = -0.0736766070478 - -1.04707893164E+16/3.92717784794E+18 = -0.0710103694284
Error Bound = -0.0710103694284 - -0.0736766070478 = 0.002666 > 0.000001
i36 = -0.0710103694284
f(i37) = -3.86031514864E+15
f'(i37) = 1.44157806326E+18
i37 = -0.0710103694284 - -3.86031514864E+15/1.44157806326E+18 = -0.0683325296034
Error Bound = -0.0683325296034 - -0.0710103694284 = 0.002678 > 0.000001
i37 = -0.0683325296034
f(i38) = -1.42328988493E+15
f'(i38) = 5.29135970462E+17
i38 = -0.0683325296034 - -1.42328988493E+15/5.29135970462E+17 = -0.0656426918975
Error Bound = -0.0656426918975 - -0.0683325296034 = 0.00269 > 0.000001
i38 = -0.0656426918975
f(i39) = -5.24801190598E+14
f'(i39) = 1.94206662828E+17
i39 = -0.0656426918975 - -5.24801190598E+14/1.94206662828E+17 = -0.0629404097875
Error Bound = -0.0629404097875 - -0.0656426918975 = 0.002702 > 0.000001
i39 = -0.0629404097875
f(i40) = -1.93522453099E+14
f'(i40) = 7.12728551094E+16
i40 = -0.0629404097875 - -1.93522453099E+14/7.12728551094E+16 = -0.0602251761575
Error Bound = -0.0602251761575 - -0.0629404097875 = 0.002715 > 0.000001
i40 = -0.0602251761575
f(i41) = -7.13687509836E+13
f'(i41) = 2.6154230491E+16
i41 = -0.0602251761575 - -7.13687509836E+13/2.6154230491E+16 = -0.0574964110727
Error Bound = -0.0574964110727 - -0.0602251761575 = 0.002729 > 0.000001
i41 = -0.0574964110727
f(i42) = -2.63227437545E+13
f'(i42) = 9.59645698904E+15
i42 = -0.0574964110727 - -2.63227437545E+13/9.59645698904E+15 = -0.0547534462697
Error Bound = -0.0547534462697 - -0.0574964110727 = 0.002743 > 0.000001
i42 = -0.0547534462697
f(i43) = -9.70974928636E+12
f'(i43) = 3.52065151964E+15
i43 = -0.0547534462697 - -9.70974928636E+12/3.52065151964E+15 = -0.0519955052374
Error Bound = -0.0519955052374 - -0.0547534462697 = 0.002758 > 0.000001
i43 = -0.0519955052374
f(i44) = -3.58218394259E+12
f'(i44) = 1.29142254216E+15
i44 = -0.0519955052374 - -3.58218394259E+12/1.29142254216E+15 = -0.0492216772875
Error Bound = -0.0492216772875 - -0.0519955052374 = 0.002774 > 0.000001
i44 = -0.0492216772875
f(i45) = -1.32178928413E+12
f'(i45) = 4.73624813591E+14
i45 = -0.0492216772875 - -1.32178928413E+12/4.73624813591E+14 = -0.0464308832957
Error Bound = -0.0464308832957 - -0.0492216772875 = 0.002791 > 0.000001
i45 = -0.0464308832957
f(i46) = -487826658810
f'(i46) = 1.73662282397E+14
i46 = -0.0464308832957 - -487826658810/1.73662282397E+14 = -0.0436218297
Error Bound = -0.0436218297 - -0.0464308832957 = 0.002809 > 0.000001
i46 = -0.0436218297
f(i47) = -180084503903
f'(i47) = 6.36592027928E+13
i47 = -0.0436218297 - -180084503903/6.36592027928E+13 = -0.0407929456423
Error Bound = -0.0407929456423 - -0.0436218297 = 0.002829 > 0.000001
i47 = -0.0407929456423
f(i48) = -66499565900.3
f'(i48) = 2.33278594243E+13
i48 = -0.0407929456423 - -66499565900.3/2.33278594243E+13 = -0.0379422954952
Error Bound = -0.0379422954952 - -0.0407929456423 = 0.002851 > 0.000001
i48 = -0.0379422954952
f(i49) = -24565458889.5
f'(i49) = 8.54498121945E+12
i49 = -0.0379422954952 - -24565458889.5/8.54498121945E+12 = -0.0350674549006
Error Bound = -0.0350674549006 - -0.0379422954952 = 0.002875 > 0.000001
i49 = -0.0350674549006
f(i50) = -9078990673.31
f'(i50) = 3.12839659393E+12
i50 = -0.0350674549006 - -9078990673.31/3.12839659393E+12 = -0.0321653322315
Error Bound = -0.0321653322315 - -0.0350674549006 = 0.002902 > 0.000001
i50 = -0.0321653322315
f(i51) = -3357497359.4
f'(i51) = 1.14456615544E+12
i51 = -0.0321653322315 - -3357497359.4/1.14456615544E+12 = -0.0292319086426
Error Bound = -0.0292319086426 - -0.0321653322315 = 0.002933 > 0.000001
i51 = -0.0292319086426
f(i52) = -1242625997.32
f'(i52) = 418385738391
i52 = -0.0292319086426 - -1242625997.32/418385738391 = -0.026261859993
Error Bound = -0.026261859993 - -0.0292319086426 = 0.00297 > 0.000001
i52 = -0.026261859993
f(i53) = -460387845.881
f'(i53) = 152757966932
i53 = -0.026261859993 - -460387845.881/152757966932 = -0.0232480214671
Error Bound = -0.0232480214671 - -0.026261859993 = 0.003014 > 0.000001
i53 = -0.0232480214671
f(i54) = -170811107.475
f'(i54) = 55687276169
i54 = -0.0232480214671 - -170811107.475/55687276169 = -0.020180694077
Error Bound = -0.020180694077 - -0.0232480214671 = 0.003067 > 0.000001
i54 = -0.020180694077
f(i55) = -63489313.9654
f'(i55) = 20260115211.4
i55 = -0.020180694077 - -63489313.9654/20260115211.4 = -0.0170469846532
Error Bound = -0.0170469846532 - -0.020180694077 = 0.003134 > 0.000001
i55 = -0.0170469846532
f(i56) = -23650443.2253
f'(i56) = 7354208562.06
i56 = -0.0170469846532 - -23650443.2253/7354208562.06 = -0.0138310786823
Error Bound = -0.0138310786823 - -0.0170469846532 = 0.003216 > 0.000001
i56 = -0.0138310786823
f(i57) = -8827848.6575
f'(i57) = 2665078105.19
i57 = -0.0138310786823 - -8827848.6575/2665078105.19 = -0.0105186621943
Error Bound = -0.0105186621943 - -0.0138310786823 = 0.003312 > 0.000001
i57 = -0.0105186621943
f(i58) = -3294060.464
f'(i58) = 967967077.897
i58 = -0.0105186621943 - -3294060.464/967967077.897 = -0.00711559142964
Error Bound = -0.00711559142964 - -0.0105186621943 = 0.003403 > 0.000001
i58 = -0.00711559142964
f(i59) = -1217496.3677
f'(i59) = 357230041.293
i59 = -0.00711559142964 - -1217496.3677/357230041.293 = -0.00370743358455
Error Bound = -0.00370743358455 - -0.00711559142964 = 0.003408 > 0.000001
i59 = -0.00370743358455
f(i60) = -432933.1975
f'(i60) = 139281791.853
i60 = -0.00370743358455 - -432933.1975/139281791.853 = -0.000599107709482
Error Bound = -0.000599107709482 - -0.00370743358455 = 0.003108 > 0.000001
i60 = -0.000599107709482
f(i61) = -136216.2992
f'(i61) = 62721961.7697
i61 = -0.000599107709482 - -136216.2992/62721961.7697 = 0.00157264035762
Error Bound = 0.00157264035762 - -0.000599107709482 = 0.002172 > 0.000001
i61 = 0.00157264035762
f(i62) = -30154.2405
f'(i62) = 37411553.5834
i62 = 0.00157264035762 - -30154.2405/37411553.5834 = 0.00237865447792
Error Bound = 0.00237865447792 - 0.00157264035762 = 0.000806 > 0.000001
i62 = 0.00237865447792
f(i63) = -2603.3354
f'(i63) = 31170948.3116
i63 = 0.00237865447792 - -2603.3354/31170948.3116 = 0.00246217248383
Error Bound = 0.00246217248383 - 0.00237865447792 = 8.4E-5 > 0.000001
i63 = 0.00246217248383
f(i64) = -24.0897
f'(i64) = 30596147.3807
i64 = 0.00246217248383 - -24.0897/30596147.3807 = 0.00246295982688
Error Bound = 0.00246295982688 - 0.00246217248383 = 1.0E-6 < 0.000001
IRR = 0.2462960%
Annual IRR = 2.9555518%
APR = 2.9959190%
At an IRR of 0.00246295982688, the net present values of the cash flows is indeed zero thus this is the correct rate that leads to an APR of 2.9959% which is still different from the one quoted in the ad as 3.022%
June 12th, 2013 at 9:54:39 AM
permalink
Quote: pacomartinYou know I still can' figure out all the non-gradient calculations. For instance look at this ADVERTISEMENT:
Adjustable-Rate Mortgage: The initial payment on a 30-year $200,000 5-year Adjustable-Rate Loan at 2.50% and 70% loan-to-value (LTV) is $790.25 with 1.625 points due at closing. The Annual Percentage Rate (APR) is 3.022%. After the initial 5 years, the principal and interest payment is $835.33. The fully indexed rate of 3.00% is in effect for the remaining 25 years and can change once every year for the remaining life of the loan. Payment does not include taxes and insurance premiums. The actual payment amount will be greater. Rate is variable and subject to change after 5 years.
$200k @ 2.50% COMPOUNDED MONTHLY for 30 years, PMT IS -$790.24
After 5 years principal is $176,150.76 @ 3.00% COMPOUNDED MONTHLY for 25 years, PMT IS -$835.33
But I am not sure how you get 3.022% ?
It seems like they "lender" is adding another $650 to costs of the loan besides the points due at closing
In your example the points were 1.625 and if we add another $650 then APR comes out to 3.022%
And when I looked at the lenders site, it had the same data for loan but it used 2 points thus when I added $650 to their loan the APR turned out to the same as they had advertised on their site as 3.052%
See the following two calculations for your loan with 1.625 points and the second loan with 2 points due at closing
guess = 2%
nper1 = 60
nper2 = 360
pmt1 = -790.241797635
pmt2 = -835.326845070
pv = 196100
fv = 0
type = 0
PV + PMT1 pvifa(i%, NPER1) + PMT2 pvifa(i%, NPER2) - PMT2 pvifa(i%, NPER1) + FV pvif(i%, NPER2) = 0
f(i) = 196100 + -790.241797635 pvifa(i%, 60) + -835.326845070 pvifa(i%, 360) - -835.326845070 pvifa(i%, 60) + 0 pvif(i%, 360)
f'(i) = 0 + -790.241797635 pvifa'(i%, 60) + -835.326845070 pvifa'(i%, 360) - -835.326845070 pvifa'(i%, 60) + 0 pvif'(i%, 360)
i0 = 0.02
f(i1) = 155934.3324
f'(i1) = 2036882.5457
i1 = 0.02 - 155934.3324/2036882.5457 = -0.056555387396
Error Bound = -0.056555387396 - 0.02 = 0.076555 > 0.000001
i1 = -0.056555387396
f(i2) = -1.8684232614E+13
f'(i2) = 6.79916706339E+15
i2 = -0.056555387396 - -1.8684232614E+13/6.79916706339E+15 = -0.0538073695227
Error Bound = -0.0538073695227 - -0.056555387396 = 0.002748 > 0.000001
i2 = -0.0538073695227
f(i3) = -6.89243436438E+12
f'(i3) = 2.49428519351E+15
i3 = -0.0538073695227 - -6.89243436438E+12/2.49428519351E+15 = -0.0510440791089
Error Bound = -0.0510440791089 - -0.0538073695227 = 0.002763 > 0.000001
i3 = -0.0510440791089
f(i4) = -2.54294327939E+12
f'(i4) = 9.14883408695E+14
i4 = -0.0510440791089 - -2.54294327939E+12/9.14883408695E+14 = -0.0482645519525
Error Bound = -0.0482645519525 - -0.0510440791089 = 0.00278 > 0.000001
i4 = -0.0482645519525
f(i5) = -938382233402
f'(i5) = 3.35506670782E+14
i5 = -0.0482645519525 - -938382233402/3.35506670782E+14 = -0.0454676411453
Error Bound = -0.0454676411453 - -0.0482645519525 = 0.002797 > 0.000001
i5 = -0.0454676411453
f(i6) = -346351992670
f'(i6) = 1.23008504291E+14
i6 = -0.0454676411453 - -346351992670/1.23008504291E+14 = -0.0426519659636
Error Bound = -0.0426519659636 - -0.0454676411453 = 0.002816 > 0.000001
i6 = -0.0426519659636
f(i7) = -127870595304
f'(i7) = 4.50863850329E+13
i7 = -0.0426519659636 - -127870595304/4.50863850329E+13 = -0.0398158415946
Error Bound = -0.0398158415946 - -0.0426519659636 = 0.002836 > 0.000001
i7 = -0.0398158415946
f(i8) = -47224274914
f'(i8) = 1.65197191869E+13
i8 = -0.0398158415946 - -47224274914/1.65197191869E+13 = -0.0369571807191
Error Bound = -0.0369571807191 - -0.0398158415946 = 0.002859 > 0.000001
i8 = -0.0369571807191
f(i9) = -17447646294.7
f'(i9) = 6.05016985829E+12
i9 = -0.0369571807191 - -17447646294.7/6.05016985829E+12 = -0.0340733532062
Error Bound = -0.0340733532062 - -0.0369571807191 = 0.002884 > 0.000001
i9 = -0.0340733532062
f(i10) = -6449605619.77
f'(i10) = 2.21455561964E+12
i10 = -0.0340733532062 - -6449605619.77/2.21455561964E+12 = -0.0311609830844
Error Bound = -0.0311609830844 - -0.0340733532062 = 0.002912 > 0.000001
i10 = -0.0311609830844
f(i11) = -2385722360.81
f'(i11) = 810001542193
i11 = -0.0311609830844 - -2385722360.81/810001542193 = -0.0282156524442
Error Bound = -0.0282156524442 - -0.0311609830844 = 0.002945 > 0.000001
i11 = -0.0282156524442
f(i12) = -883258687.567
f'(i12) = 295980406065
i12 = -0.0282156524442 - -883258687.567/295980406065 = -0.0252314728517
Error Bound = -0.0252314728517 - -0.0282156524442 = 0.002984 > 0.000001
i12 = -0.0252314728517
f(i13) = -327387309.334
f'(i13) = 108013584797
i13 = -0.0252314728517 - -327387309.334/108013584797 = -0.022200490129
Error Bound = -0.022200490129 - -0.0252314728517 = 0.003031 > 0.000001
i13 = -0.022200490129
f(i14) = -121536125.582
f'(i14) = 39350785240.5
i14 = -0.022200490129 - -121536125.582/39350785240.5 = -0.0191119589894
Error Bound = -0.0191119589894 - -0.022200490129 = 0.003089 > 0.000001
i14 = -0.0191119589894
f(i15) = -45207039.4586
f'(i15) = 14305435855.6
i15 = -0.0191119589894 - -45207039.4586/14305435855.6 = -0.0159518288183
Error Bound = -0.0159518288183 - -0.0191119589894 = 0.00316 > 0.000001
i15 = -0.0159518288183
f(i16) = -16853274.7449
f'(i16) = 5188851482.15
i16 = -0.0159518288183 - -16853274.7449/5188851482.15 = -0.0127038509559
Error Bound = -0.0127038509559 - -0.0159518288183 = 0.003248 > 0.000001
i16 = -0.0127038509559
f(i17) = -6293111.5412
f'(i17) = 1880373293.37
i17 = -0.0127038509559 - -6293111.5412/1880373293.37 = -0.00935711572874
Error Bound = -0.00935711572874 - -0.0127038509559 = 0.003347 > 0.000001
i17 = -0.00935711572874
f(i18) = -2344575.9213
f'(i18) = 685014528.051
i18 = -0.00935711572874 - -2344575.9213/685014528.051 = -0.0059344497483
Error Bound = -0.0059344497483 - -0.00935711572874 = 0.003423 > 0.000001
i18 = -0.0059344497483
f(i19) = -859566.1191
f'(i19) = 255922082.369
i19 = -0.0059344497483 - -859566.1191/255922082.369 = -0.00257574732165
Error Bound = -0.00257574732165 - -0.0059344497483 = 0.003359 > 0.000001
i19 = -0.00257574732165
f(i20) = -297358.5776
f'(i20) = 103404367.437
i20 = -0.00257574732165 - -297358.5776/103404367.437 = 0.000299939508084
Error Bound = 0.000299939508084 - -0.00257574732165 = 0.002876 > 0.000001
i20 = 0.000299939508084
f(i21) = -86230.1206
f'(i21) = 50424856.3704
i21 = 0.000299939508084 - -86230.1206/50424856.3704 = 0.00201001122281
Error Bound = 0.00201001122281 - 0.000299939508084 = 0.00171 > 0.000001
i21 = 0.00201001122281
f(i22) = -15232.4843
f'(i22) = 33862569.5815
i22 = 0.00201001122281 - -15232.4843/33862569.5815 = 0.00245984372169
Error Bound = 0.00245984372169 - 0.00201001122281 = 0.00045 > 0.000001
i22 = 0.00245984372169
f(i23) = -745.3593
f'(i23) = 30612006.8406
i23 = 0.00245984372169 - -745.3593/30612006.8406 = 0.00248419231413
Error Bound = 0.00248419231413 - 0.00245984372169 = 2.4E-5 > 0.000001
i23 = 0.00248419231413
f(i24) = -2.0151
f'(i24) = 30446658.2322
i24 = 0.00248419231413 - -2.0151/30446658.2322 = 0.00248425849927
Error Bound = 0.00248425849927 - 0.00248419231413 = 0 < 0.000001
IRR = 0.2484258%
Annual IRR = 2.9811102%
APR = 3.0221816%
----------------------------------
guess = 2%
nper1 = 60
nper2 = 360
pmt1 = -790.241797635
pmt2 = -835.326845070
pv = 195350
fv = 0
type = 0
PV + PMT1 pvifa(i%, NPER1) + PMT2 pvifa(i%, NPER2) - PMT2 pvifa(i%, NPER1) + FV pvif(i%, NPER2) = 0
f(i) = 195350 + -790.241797635 pvifa(i%, 60) + -835.326845070 pvifa(i%, 360) - -835.326845070 pvifa(i%, 60) + 0 pvif(i%, 360)
f'(i) = 0 + -790.241797635 pvifa'(i%, 60) + -835.326845070 pvifa'(i%, 360) - -835.326845070 pvifa'(i%, 60) + 0 pvif'(i%, 360)
i0 = 0.02
f(i1) = 155184.3324
f'(i1) = 2036882.5457
i1 = 0.02 - 155184.3324/2036882.5457 = -0.0561871776523
Error Bound = -0.0561871776523 - 0.02 = 0.076187 > 0.000001
i1 = -0.0561871776523
f(i2) = -1.63419865829E+13
f'(i2) = 5.94250053104E+15
i2 = -0.0561871776523 - -1.63419865829E+13/5.94250053104E+15 = -0.053437159121
Error Bound = -0.053437159121 - -0.0561871776523 = 0.00275 > 0.000001
i2 = -0.053437159121
f(i3) = -6.02851785762E+12
f'(i3) = 2.17997141615E+15
i3 = -0.053437159121 - -6.02851785762E+12/2.17997141615E+15 = -0.0506717476973
Error Bound = -0.0506717476973 - -0.053437159121 = 0.002765 > 0.000001
i3 = -0.0506717476973
f(i4) = -2.22425420591E+12
f'(i4) = 7.99576429683E+14
i4 = -0.0506717476973 - -2.22425420591E+12/7.99576429683E+14 = -0.047889957085
Error Bound = -0.047889957085 - -0.0506717476973 = 0.002782 > 0.000001
i4 = -0.047889957085
f(i5) = -820803607814
f'(i5) = 2.9321275838E+14
i5 = -0.047889957085 - -820803607814/2.9321275838E+14 = -0.045090612294
Error Bound = -0.045090612294 - -0.047889957085 = 0.002799 > 0.000001
i5 = -0.045090612294
f(i6) = -302964213001
f'(i6) = 1.07498314359E+14
i6 = -0.045090612294 - -302964213001/1.07498314359E+14 = -0.0422722963529
Error Bound = -0.0422722963529 - -0.045090612294 = 0.002818 > 0.000001
i6 = -0.0422722963529
f(i7) = -111856607244
f'(i7) = 3.93997320118E+13
i7 = -0.0422722963529 - -111856607244/3.93997320118E+13 = -0.0394332768588
Error Bound = -0.0394332768588 - -0.0422722963529 = 0.002839 > 0.000001
i7 = -0.0394332768588
f(i8) = -41312144088.7
f'(i8) = 1.44353469198E+13
i8 = -0.0394332768588 - -41312144088.7/1.44353469198E+13 = -0.0365714028547
Error Bound = -0.0365714028547 - -0.0394332768588 = 0.002862 > 0.000001
i8 = -0.0365714028547
f(i9) = -15264281542.6
f'(i9) = 5.28643126938E+12
i9 = -0.0365714028547 - -15264281542.6/5.28643126938E+12 = -0.0336839574753
Error Bound = -0.0336839574753 - -0.0365714028547 = 0.002887 > 0.000001
i9 = -0.0336839574753
f(i10) = -5642965726.27
f'(i10) = 1.93483295988E+12
i10 = -0.0336839574753 - -5642965726.27/1.93483295988E+12 = -0.0307674443482
Error Bound = -0.0307674443482 - -0.0336839574753 = 0.002917 > 0.000001
i10 = -0.0307674443482
f(i11) = -2087562408.59
f'(i11) = 707607883301
i11 = -0.0307674443482 - -2087562408.59/707607883301 = -0.0278172759599
Error Bound = -0.0278172759599 - -0.0307674443482 = 0.00295 > 0.000001
i11 = -0.0278172759599
f(i12) = -772978572.996
f'(i12) = 258525388538
i12 = -0.0278172759599 - -772978572.996/258525388538 = -0.0248273236873
Error Bound = -0.0248273236873 - -0.0278172759599 = 0.00299 > 0.000001
i12 = -0.0248273236873
f(i13) = -286563751.217
f'(i13) = 94325668848.5
i13 = -0.0248273236873 - -286563751.217/94325668848.5 = -0.0217892985695
Error Bound = -0.0217892985695 - -0.0248273236873 = 0.003038 > 0.000001
i13 = -0.0217892985695
f(i14) = -106406907.613
f'(i14) = 34354956116.6
i14 = -0.0217892985695 - -106406907.613/34354956116.6 = -0.0186920188858
Error Bound = -0.0186920188858 - -0.0217892985695 = 0.003097 > 0.000001
i14 = -0.0186920188858
f(i15) = -39591379.3339
f'(i15) = 12485308375.8
i15 = -0.0186920188858 - -39591379.3339/12485308375.8 = -0.015520981524
Error Bound = -0.015520981524 - -0.0186920188858 = 0.003171 > 0.000001
i15 = -0.015520981524
f(i16) = -14764235.5806
f'(i16) = 4527407169.75
i16 = -0.015520981524 - -14764235.5806/4527407169.75 = -0.0122599018315
Error Bound = -0.0122599018315 - -0.015520981524 = 0.003261 > 0.000001
i16 = -0.0122599018315
f(i17) = -5513483.6572
f'(i17) = 1640872341.72
i17 = -0.0122599018315 - -5513483.6572/1640872341.72 = -0.00889980883887
Error Bound = -0.00889980883887 - -0.0122599018315 = 0.00336 > 0.000001
i17 = -0.00889980883887
f(i18) = -2052256.5524
f'(i18) = 598733093.188
i18 = -0.00889980883887 - -2052256.5524/598733093.188 = -0.00547214369775
Error Bound = -0.00547214369775 - -0.00889980883887 = 0.003428 > 0.000001
i18 = -0.00547214369775
f(i19) = -749302.5014
f'(i19) = 225045344.66
i19 = -0.00547214369775 - -749302.5014/225045344.66 = -0.0021425813711
Error Bound = -0.0021425813711 - -0.00547214369775 = 0.00333 > 0.000001
i19 = -0.0021425813711
f(i20) = -255735.3971
f'(i20) = 92463748.2979
i20 = -0.0021425813711 - -255735.3971/92463748.2979 = 0.000623209566477
Error Bound = 0.000623209566477 - -0.0021425813711 = 0.002766 > 0.000001
i20 = 0.000623209566477
f(i21) = -71292.0644
f'(i21) = 46687995.6846
i21 = 0.000623209566477 - -71292.0644/46687995.6846 = 0.00215019874949
Error Bound = 0.00215019874949 - 0.000623209566477 = 0.001527 > 0.000001
i21 = 0.00215019874949
f(i22) = -11309.713
f'(i22) = 32808439.105
i22 = 0.00215019874949 - -11309.713/32808439.105 = 0.00249491837894
Error Bound = 0.00249491837894 - 0.00215019874949 = 0.000345 > 0.000001
i22 = 0.00249491837894
f(i23) = -425.8313
f'(i23) = 30374148.5226
i23 = 0.00249491837894 - -425.8313/30374148.5226 = 0.00250893791066
Error Bound = 0.00250893791066 - 0.00249491837894 = 1.4E-5 > 0.000001
i23 = 0.00250893791066
f(i24) = -0.6626
f'(i24) = 30279677.2518
i24 = 0.00250893791066 - -0.6626/30279677.2518 = 0.00250895979404
Error Bound = 0.00250895979404 - 0.00250893791066 = 0 < 0.000001
IRR = 0.2508960%
Annual IRR = 3.0107518%
APR = 3.0526474%
June 12th, 2013 at 10:00:30 AM
permalink
Quote: FinEngineerOK, it would seem that the use of extended TVM equation when cash flows are compounded are generating erroneous IRR values
Just to complicate matters a bit, are you aware that for some real patterns of cash flow there is no defined IRR at all? I don't mean a negative return, but one for which the iteration never converges.
I first encountered this in the case of industrial capital investments that are financed over time (cash outflow over a series of periods) and which result in long-term financial savings for the operations (cash inflows over an extended series of periods) and for which there is a tax credit that leads to positive cash flow in year #1.
A very high assumed rate of return leads to a calculated positive NPV (dominated by the effect of the tax credit) while a very low assumed rate of return also leads to a positive NPV (dominated by the extended period of savings). In most cases, there is at least one intermediate rate of return that gives a zero NPV, and that (those) are considered the IRR. In some cases, the NPV is positive for any rate of return used in the calculation, so there is no defined IRR. Of course, if those cash flows are really achieved, then the investment is a good one, even though there is no way to calculate an IRR.
June 12th, 2013 at 11:35:59 AM
permalink
Quote: DocJust to complicate matters a bit, are you aware that for some real patterns of cash flow there is no defined IRR at all? I don't mean a negative return, but one for which the iteration never converges.
I dunno, there was this chap on Math.StackExchange.com the other day, whose post I was reading and to my surprise he was using no guessing games of Sir Isaac Newton to perform iterative calculations for IRR. This guy was using Taylor Series to approximate the continuously compounded rate of return from a n-degree polynomial. And depending on the order of his equation, he had shown three resultant equations last of which looked like a quadratic equation that he solved with quadratic formula to get two different IRRs.
Now I didn't find time to check his answer but if those two rates were in fact one of the two IRR values when interest was compounded continuously then turning those rates into nominal IRR would be a simple matter.
As I said, I run a few finance sites that have calculators and Excel add-ins that I researched, designed and developed based on my own learning of financial math. I have no degree, no diploma but just a clean slate...
June 12th, 2013 at 1:03:12 PM
permalink
Quote: DocIn some cases, the NPV is positive for any rate of return used in the calculation, so there is no defined IRR. Of course, if those cash flows are really achieved, then the investment is a good one, even though there is no way to calculate an IRR.
That makes sense, but no one should compare an investment on IRR alone.
The investment with a lower IRR may have higher NPV for a given rate of return.
June 12th, 2013 at 1:13:52 PM
permalink
Quote: pacomartin
That makes sense, but no one should compare an investment on IRR alone.
The investment with a lower IRR may have higher NPV for a given rate of return.
How about if I give you 60 different Excel functions just for analyzing investments. I said 60 of them, I dunno thats way too many different metrics to find the most suitable of investments
http://tadxl.com/tadxl_functions_reference_a_to_z.html
I mean have you really heard of something called a True or Real payback period? What good is the recovery of just the initial cash outlay if your investment hasn't recovered all costs. For an investment to be profitable, you must recover all costs. And there are 16 variants of payback period functions to choose from
June 12th, 2013 at 5:07:19 PM
permalink
Quote: FinEngineerI mean have you really heard of something called a True or Real payback period? What good is the recovery of just the initial cash outlay if your investment hasn't recovered all costs. For an investment to be profitable, you must recover all costs. And there are 16 variants of payback period functions to choose from
I will look at your functions for more complex financial calculations. I intended the original post for much simpler questions.
For instance if you are financing $28,000 of a car after downpayment and the dealer offers you a lease with a $400 payment and a residual value of $17,000 then what interest are you paying? Most people don't know that the interest rate is 4.97% or money factor of 0.002072.
By the same token $28,000. financed for 7 years at 5.00%, results in a payment of $395.75 and a loan balance of $17,184.61 after 3 years.
If you can only get 5.50% rate, payments jump to $402.36 with loan balance of $17,301 .
In general, I think many people are getting ripped off with leases. They are attracted to the idea of turning the car in after 36 months with no muss or fuss. But generally, if you take a 7 year loan, you can usually get a trade-in for the loan balance after 36 months.
If you are taking a 7 year loan, the dealership is not contractually obligated give you a trade-in for the loan balance, but if you maintain the car like the lease requires you to, it should be no problem.
Even well meaning people go over the mileage all the time. If you are over 3000 miles on a three year lease, that is $450. A small ding and the dealer can easily assess you $100 to fix it. Maybe $50 for a broken button.
If you return with a car with a 7 year lease, and you have exceeded mileage by that amount, and you have a ding, you are free to negotiate. If you say, I refuse to accept less than the loan balance or I will go elsewhere to purchase a car, the dealer will figure he can fix the dent himself, replace the button, and the small extra mileage won't hurt the resale much.
It is also much easier to turn the car in at 34 months, or 40 months, or whatever is convenient for you.
June 13th, 2013 at 2:39:48 AM
permalink
Quote: pacomartin
I will look at your functions for more complex financial calculations. I intended the original post for much simpler questions.
Yes Excel TVM functions of (RATE, NPER, PMT, PV & FV) to a limit can answer questions such as the one you listed
As for the APR on ARM, Excel RATE is not going to do it
As I said earlier, I would now have to dig a deeper into loans and mortgages and would implement such functionality in upcoming tadXL v3.0
Already the tadRATE function in tadXL v2.0 itself is complicated enough and I would not want to amend it further. I will now have to create a new function called tadRATESchedule which will permit a user to enter a loan amount (PV), and a schedule of interest rates so the function will internally find different payments that are due for the adjusted rate mortgages. The user will also have to enter a schedule of NPER values. The function will then internally calculate the series of payments, and finally find the APR of the ARM. And just like it's older counterpart tadRATE, this new function will also allow for gradient so that allowing for payments that grow by a growth, shrink rate or increase, decrease by a constant money amount. But then the growth rate itself may not be constant either so a schedule of growth rates will be required as input. And just like tadRATE, the gradient may not coincide with payments. And further the payments may not commence immediately and we have a deferred annuity. It can get even more complex if required. :)
So I am thinking there will be six new TVM functions in tadXL v3.0
In tadXL v2.0 the following functions were extended to allow for gradient
- tadRATE
- tadGRADIENT
- tadNPER
- tadPMT
- tadPV
- tadFV
In tadXL v3.0 the following functions will be added to allow for schedule of rates, payments, and number of periods, and gradient
- tadRATESchedule
- tadGRADIENTSchedule
- tadNPERSchedule
- tadPMTSchedule
- tadPVSchedule
- tadFVSchedule
But I have only 14 hours per day to work with, and still haven't gotten a chance to create the localized version of tadXL v2.0 for Euro zone countries. Got the German one done but haven't found a way to create web pages in German for the documentation as the only language that I know if little is English
