Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adjustable Rate APR
Does anyone have a formula to calculate an APR for an adjustable rate mortgage?
for example if I have a 200,000 - 30 year mortgage that has a fixed rate of 6% for 3 years and then adjusts to a rate of 7.75 every year after that after that, with 1500 in finance charges. So far I can figure out the APR without adjustment of =rate(360,PMT(6%/12,360,200000),200000-1500)*12 Any suggestions to complete the formula? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adjustable Rate APR
"Xhawk57" wrote:
Does anyone have a formula to calculate an APR for an adjustable rate mortgage? for example if I have a 200,000 - 30 year mortgage that has a fixed rate of 6% for 3 years and then adjusts to a rate of 7.75 every year after that after that, with 1500 in finance charges. So far I can figure out the APR without adjustment of =rate(360,PMT(6%/12,360,200000),200000-1500)*12 Any suggestions to complete the formula? First, it is important to note that the following is from a US point of view. In particular, it might be different for Canada. Second, it is important to note that the following refers to the lender's APR, which is what US law requires the lender to report. This is very different from "effective" annual interest rate -- the "borrower's APR". According to [1], the lender's APR is the IRR over the term of the loan contract. IMHO, this is difficult to compute using Excel because IRR() requires a cell for each cash flow. In contrast, the HP12 calculator allows for grouping similar cash flows. Also, according to [1] and [2], the annualized APR is simply 12 times the monthly APR -- another difference between the lender's APR and the borrower's APR. Note: I have not verified that assertion by looking at US law myself, but I take it for granted, given that it is stated in two different academic sources. But academia is not always in touch with reality. I feel certain that there is an easier way to formulate this computation. But off-hand, one way to compute the lender's APR using Excel's IRR() might be: 1. In A1, put =-(200000-1500): the contract loan less the loan fees. Note: The choice of sign is arbitrary, but it must be the opposite of the sign used for payments (below). 2. In A2, put =PMT(6%/12,30*12,-200000): the monthly payment for the first 3 years based on the initial contract loan terms. 3. In A3, put =A2, and copy down through A37 so that A2:A37 comprise the first 36 payments. 4. In A38, put =PMT(7.75%/12,27*12, -FV(6%/12,3*12,A37,-200000): the payment for the remaining 27 years of the contract loan, based on the outstanding loan balance after the first 3 years. 5. In A39, put =A38, and copy down through A361. Thus, A38:A361 comprise the last 27 years of monthly payments. 6. In some cell, compute =12*IRR(A1:A361). Be sure to format as Percentage with 2 decimal places. I get 7.36%. It is important to note that while the lender's APR might be useful (or not!) for comparing loans, it serves no other purpose. The number (7.36%) tells us nothing about the actual interest paid on the loan, even if we assume that the future ARM interest rate estimate is accurate (not!). For some discussion of this, see [2]. Also see [3] for an explanation why the lender's APR might not even be suitable for comparison (klunk!). Bottom line: Unless you are just curious or an academic who wants to understand how loan terms are created (see [1]), I don't think this "APR" is worth the trouble to compute. ----- [1] http://web.mit.edu/11.431j/www/Fall91602/431_GMch17.ppt [2] http://mcb.unco.edu/web/fs/wps/worki...sion%20_2_.pdf [3] http://www.mtg-net.com/sfaq/faq/apr.htm |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adjustable Rate APR
I got NUM# when i follow your instructions, I rechecked several times and
could not figure out what i did wrong. I am hoping to find a formula that will calculate an adjustable rate APR in a single cell. so far i have come up with this: Loan Amount 200000 E2 term 360 E3 fixed period 36 E4 finance Charges 1500 E5 Initial rate 6.000% E6 index 5.000% E7 Margin 2.750% E8 Adjustable APR 7.65667%: =(((RATE(E3,PMT(E6/12,E3,E2),E2-E5)*12)*(E4/E3))+((RATE(E3-E4,PMT((E7+E8)/12,E3-E4,E2),E2-E5)*12)*((E3-E4)/E3))) I have calculator that gave an apr of 6.839% any thoughts? " wrote: "Xhawk57" wrote: Does anyone have a formula to calculate an APR for an adjustable rate mortgage? for example if I have a 200,000 - 30 year mortgage that has a fixed rate of 6% for 3 years and then adjusts to a rate of 7.75 every year after that after that, with 1500 in finance charges. So far I can figure out the APR without adjustment of =rate(360,PMT(6%/12,360,200000),200000-1500)*12 Any suggestions to complete the formula? First, it is important to note that the following is from a US point of view. In particular, it might be different for Canada. Second, it is important to note that the following refers to the lender's APR, which is what US law requires the lender to report. This is very different from "effective" annual interest rate -- the "borrower's APR". According to [1], the lender's APR is the IRR over the term of the loan contract. IMHO, this is difficult to compute using Excel because IRR() requires a cell for each cash flow. In contrast, the HP12 calculator allows for grouping similar cash flows. Also, according to [1] and [2], the annualized APR is simply 12 times the monthly APR -- another difference between the lender's APR and the borrower's APR. Note: I have not verified that assertion by looking at US law myself, but I take it for granted, given that it is stated in two different academic sources. But academia is not always in touch with reality. I feel certain that there is an easier way to formulate this computation. But off-hand, one way to compute the lender's APR using Excel's IRR() might be: 1. In A1, put =-(200000-1500): the contract loan less the loan fees. Note: The choice of sign is arbitrary, but it must be the opposite of the sign used for payments (below). 2. In A2, put =PMT(6%/12,30*12,-200000): the monthly payment for the first 3 years based on the initial contract loan terms. 3. In A3, put =A2, and copy down through A37 so that A2:A37 comprise the first 36 payments. 4. In A38, put =PMT(7.75%/12,27*12, -FV(6%/12,3*12,A37,-200000): the payment for the remaining 27 years of the contract loan, based on the outstanding loan balance after the first 3 years. 5. In A39, put =A38, and copy down through A361. Thus, A38:A361 comprise the last 27 years of monthly payments. 6. In some cell, compute =12*IRR(A1:A361). Be sure to format as Percentage with 2 decimal places. I get 7.36%. It is important to note that while the lender's APR might be useful (or not!) for comparing loans, it serves no other purpose. The number (7.36%) tells us nothing about the actual interest paid on the loan, even if we assume that the future ARM interest rate estimate is accurate (not!). For some discussion of this, see [2]. Also see [3] for an explanation why the lender's APR might not even be suitable for comparison (klunk!). Bottom line: Unless you are just curious or an academic who wants to understand how loan terms are created (see [1]), I don't think this "APR" is worth the trouble to compute. ----- [1] http://web.mit.edu/11.431j/www/Fall91602/431_GMch17.ppt [2] http://mcb.unco.edu/web/fs/wps/worki...sion%20_2_.pdf [3] http://www.mtg-net.com/sfaq/faq/apr.htm |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adjustable Rate APR
please disreguard the 6.839%, obviously that is incorrect.
"Xhawk57" wrote: I got NUM# when i follow your instructions, I rechecked several times and could not figure out what i did wrong. I am hoping to find a formula that will calculate an adjustable rate APR in a single cell. so far i have come up with this: Loan Amount 200000 E2 term 360 E3 fixed period 36 E4 finance Charges 1500 E5 Initial rate 6.000% E6 index 5.000% E7 Margin 2.750% E8 Adjustable APR 7.65667%: =(((RATE(E3,PMT(E6/12,E3,E2),E2-E5)*12)*(E4/E3))+((RATE(E3-E4,PMT((E7+E8)/12,E3-E4,E2),E2-E5)*12)*((E3-E4)/E3))) I have calculator that gave an apr of 6.839% any thoughts? " wrote: "Xhawk57" wrote: Does anyone have a formula to calculate an APR for an adjustable rate mortgage? for example if I have a 200,000 - 30 year mortgage that has a fixed rate of 6% for 3 years and then adjusts to a rate of 7.75 every year after that after that, with 1500 in finance charges. So far I can figure out the APR without adjustment of =rate(360,PMT(6%/12,360,200000),200000-1500)*12 Any suggestions to complete the formula? First, it is important to note that the following is from a US point of view. In particular, it might be different for Canada. Second, it is important to note that the following refers to the lender's APR, which is what US law requires the lender to report. This is very different from "effective" annual interest rate -- the "borrower's APR". According to [1], the lender's APR is the IRR over the term of the loan contract. IMHO, this is difficult to compute using Excel because IRR() requires a cell for each cash flow. In contrast, the HP12 calculator allows for grouping similar cash flows. Also, according to [1] and [2], the annualized APR is simply 12 times the monthly APR -- another difference between the lender's APR and the borrower's APR. Note: I have not verified that assertion by looking at US law myself, but I take it for granted, given that it is stated in two different academic sources. But academia is not always in touch with reality. I feel certain that there is an easier way to formulate this computation. But off-hand, one way to compute the lender's APR using Excel's IRR() might be: 1. In A1, put =-(200000-1500): the contract loan less the loan fees. Note: The choice of sign is arbitrary, but it must be the opposite of the sign used for payments (below). 2. In A2, put =PMT(6%/12,30*12,-200000): the monthly payment for the first 3 years based on the initial contract loan terms. 3. In A3, put =A2, and copy down through A37 so that A2:A37 comprise the first 36 payments. 4. In A38, put =PMT(7.75%/12,27*12, -FV(6%/12,3*12,A37,-200000): the payment for the remaining 27 years of the contract loan, based on the outstanding loan balance after the first 3 years. 5. In A39, put =A38, and copy down through A361. Thus, A38:A361 comprise the last 27 years of monthly payments. 6. In some cell, compute =12*IRR(A1:A361). Be sure to format as Percentage with 2 decimal places. I get 7.36%. It is important to note that while the lender's APR might be useful (or not!) for comparing loans, it serves no other purpose. The number (7.36%) tells us nothing about the actual interest paid on the loan, even if we assume that the future ARM interest rate estimate is accurate (not!). For some discussion of this, see [2]. Also see [3] for an explanation why the lender's APR might not even be suitable for comparison (klunk!). Bottom line: Unless you are just curious or an academic who wants to understand how loan terms are created (see [1]), I don't think this "APR" is worth the trouble to compute. ----- [1] http://web.mit.edu/11.431j/www/Fall91602/431_GMch17.ppt [2] http://mcb.unco.edu/web/fs/wps/worki...sion%20_2_.pdf [3] http://www.mtg-net.com/sfaq/faq/apr.htm |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Adjustable Rate APR
"Xhawk57" wrote:
I got NUM# when i follow your instructions, I rechecked several times and could not figure out what i did wrong. Sorry: I forgot to type the "guess" argument for IRR() when I posted earlier. The last instruction should be: 6. In some cell, compute =12*IRR(A1:A361,7%/12). Be sure to format as Percentage with 2 decimal places. I get 7.36%. Presumably you fixed the other typo in my previous posting: a missing close-parenthesis for PMT() in step #4. I am hoping to find a formula that will calculate an adjustable rate APR in a single cell. So am I :-). so far i have come up with this: Loan Amount 200000 E2 term 360 E3 fixed period 36 E4 finance Charges 1500 E5 Initial rate 6.000% E6 index 5.000% E7 Margin 2.750% E8 Adjustable APR 7.65667%: =(((RATE(E3,PMT(E6/12,E3,E2),E2-E5)*12)*(E4/E3)) +((RATE(E3-E4,PMT((E7+E8)/12,E3-E4,E2),E2-E5)*12)*((E3-E4)/E3))) [....] any thoughts? You are attempting to compute a weighted average of the two interest rates. I do not believe that is mathematically correct. But it might become a reasonable estimate if done correctly, given the big difference of periods (36 v. 324 months). I quibble with your choice of E2 for PV in the PMT() function for the 2nd rate period and your choice of E2-E5 for PV in the RATE() function for the 2nd rate period. I was going to explain further and even offer an alternative method of estimation derived from the mathematical IRR formulation. But now I am not sure I know how to compute the ARM APR correctly -- or at least not according to convention. I discovered two different ARM APR calculators on the web that compute an APR of 7.554%, not 7.358% as I do. At this time, I cannot explain the difference. The difference is strange because we do agree on the general structure of the loan, namely: 36 months of payments of $1199.10 and 324 months of payments of $1417.11 for a $200,000 loan with $1500 in loan fees, including any prepaid interest. So I must withhold further comment until I or someone can explain the ARM APR derivation correctly. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
FVSCHEDULE should allow cell reference for interest rate schedule | Excel Worksheet Functions | |||
XNPV vs. NPV(quarterly) different results | Excel Worksheet Functions | |||
How do I calculate APR for an adjustable rate mortgage? | Excel Discussion (Misc queries) | |||
subtraction of times, convert & multiply by a conditioned rate | Excel Worksheet Functions | |||
Is there an adjustable rate amortization schedule in Excel? | Excel Worksheet Functions |