Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
I need to calculate an APR for a mortgage with a balloon payment.*
I've tried oodles of combinations of RATE(), PMT(), FV().* An example might be a $100,000 home, $500 in loan fees financed in the mortgage, an annual rate of 6.5%, 7 year term, payments amortized over 12 years.* The answer is ~7.02. Help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
"Jeff" wrote:
I need to calculate an APR for a mortgage with a balloon payment. I (and Fred) can certainly help you with that. But I wonder if you have omitted or misstated some critical information. For example, are payments monthly? Is there any downpayment, or is $100,000 the amount of the loan? Also, what is the jurisdiction of the loan: US; Canada? Assuming monthly payments and a loan of $100,000, and assuming a US loan, here is how I would do the calculation. Payment in A2: =round(pmt(6.5%/12, 12*12, -100000),2) Note: Although rounding is necessary for real-world loan computation, many people do not do it, including loan officers who are providing a quote, not the actual loan contract. Balloon payment in A3: =round(fv(6.5%/12,12*7,A2,-100000),2) APR: =12*RATE(12*7,A2,-(100000-500),A3) However, my result is only about 6.61%. ----- original message ----- "Jeff" wrote in message ... I need to calculate an APR for a mortgage with a balloon payment. I've tried oodles of combinations of RATE(), PMT(), FV(). An example might be a $100,000 home, $500 in loan fees financed in the mortgage, an annual rate of 6.5%, 7 year term, payments amortized over 12 years. The answer is ~7.02. Help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
I wrote:
"Jeff" wrote: The answer is ~7.02. [....] However, my result is only about 6.61%. If you tell me the source of your expectation, I might be able to explain why you might never get the expected result. ----- original message ----- "Joe User" <joeu2004 wrote in message ... "Jeff" wrote: I need to calculate an APR for a mortgage with a balloon payment. I (and Fred) can certainly help you with that. But I wonder if you have omitted or misstated some critical information. For example, are payments monthly? Is there any downpayment, or is $100,000 the amount of the loan? Also, what is the jurisdiction of the loan: US; Canada? Assuming monthly payments and a loan of $100,000, and assuming a US loan, here is how I would do the calculation. Payment in A2: =round(pmt(6.5%/12, 12*12, -100000),2) Note: Although rounding is necessary for real-world loan computation, many people do not do it, including loan officers who are providing a quote, not the actual loan contract. Balloon payment in A3: =round(fv(6.5%/12,12*7,A2,-100000),2) APR: =12*RATE(12*7,A2,-(100000-500),A3) However, my result is only about 6.61%. ----- original message ----- "Jeff" wrote in message ... I need to calculate an APR for a mortgage with a balloon payment. I've tried oodles of combinations of RATE(), PMT(), FV(). An example might be a $100,000 home, $500 in loan fees financed in the mortgage, an annual rate of 6.5%, 7 year term, payments amortized over 12 years. The answer is ~7.02. Help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
I gave you the wrong info to get my desired result. Not enough
sleep! Yours seems to work and helps me. Thanks. But what does the 12 * 7 represent? On Apr 8, 2:42*am, "Joe User" <joeu2004 wrote: I wrote: "Jeff" wrote: The answer is ~7.02. [....] However, my result is only about 6.61%. If you tell me the source of your expectation, I might be able to explain why you might never get the expected result. ----- original message ----- "Joe User" <joeu2004 wrote in message ... "Jeff" wrote: I need to calculate an APR for a mortgage with a balloon payment. I (and Fred) can certainly help you with that. *But I wonder if you have omitted or misstated some critical information. For example, are payments monthly? *Is there any downpayment, or is $100,000 the amount of the loan? *Also, what is the jurisdiction of the loan: *US; Canada? Assuming monthly payments and a loan of $100,000, and assuming a US loan, here is how I would do the calculation. Payment in A2: =round(pmt(6.5%/12, 12*12, -100000),2) Note: *Although rounding is necessary for real-world loan computation, many people do not do it, including loan officers who are providing a quote, not the actual loan contract. Balloon payment in A3: =round(fv(6.5%/12,12*7,A2,-100000),2) APR: =12*RATE(12*7,A2,-(100000-500),A3) However, my result is only about 6.61%. ----- original message ----- "Jeff" wrote in message ... I need to calculate an APR for a mortgage with a balloon payment. I've tried oodles of combinations of RATE(), PMT(), FV(). An example might be a $100,000 home, $500 in loan fees financed in the mortgage, an annual rate of 6.5%, 7 year term, payments amortized over 12 years. The answer is ~7.02. *Help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
"Jeff" wrote:
what does the 12 * 7 represent? 7 for the term of the loan in years. 12 for the number of payments per year, assumed to be monthly. I used 12*12 in the PMT formula because you had said "payments amortized over 12 years". ----- original message ----- On Apr 8, 2:42 am, "Joe User" <joeu2004 wrote: I wrote: "Jeff" wrote: The answer is ~7.02. [....] However, my result is only about 6.61%. If you tell me the source of your expectation, I might be able to explain why you might never get the expected result. ----- original message ----- "Joe User" <joeu2004 wrote in message ... "Jeff" wrote: I need to calculate an APR for a mortgage with a balloon payment. I (and Fred) can certainly help you with that. But I wonder if you have omitted or misstated some critical information. For example, are payments monthly? Is there any downpayment, or is $100,000 the amount of the loan? Also, what is the jurisdiction of the loan: US; Canada? Assuming monthly payments and a loan of $100,000, and assuming a US loan, here is how I would do the calculation. Payment in A2: =round(pmt(6.5%/12, 12*12, -100000),2) Note: Although rounding is necessary for real-world loan computation, many people do not do it, including loan officers who are providing a quote, not the actual loan contract. Balloon payment in A3: =round(fv(6.5%/12,12*7,A2,-100000),2) APR: =12*RATE(12*7,A2,-(100000-500),A3) However, my result is only about 6.61%. ----- original message ----- "Jeff" wrote in message ... I need to calculate an APR for a mortgage with a balloon payment. I've tried oodles of combinations of RATE(), PMT(), FV(). An example might be a $100,000 home, $500 in loan fees financed in the mortgage, an annual rate of 6.5%, 7 year term, payments amortized over 12 years. The answer is ~7.02. Help! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
The 12 would be 12 months in a year and the 7 is a 7 year loan. The 6.5% is not correct if you are caculatting US interest. I used the formula =(1+(0.065/360))^30 which gave 1.005431 So you would use in the PMT formula .005432 ..065/12 = .005417 which isn't as accurate as the number from my formula. Either case you wouldn't get the same numbers as the bank gives you. the bank caculations where based on hand caculations made over a 100 years ago before computers and are the standards by law. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193845 http://www.thecodecage.com/forumz |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
"joel" wrote:
The 6.5% is not correct if you are caculatting US interest. I used the formula =(1+(0.065/360))^30 which gave 1.005431 So you would use in the PMT formula .005432 That is incorrect specifically for the US. You should review the Truth in Lending Act, notably appendix J. 6.5% was the given annual interest rate. The periodic interest is determined simply by dividing that by the number of periods per year. I assumed monthly, since Jeff did not specify. Even if the periodic unit were daily, the divisor would be 365, not 360. In any case, the periodic interest rate is never compounded to arrive at the APR. The APR is defined to be "the nominal annual percentage rate determined by multiplying the unit-period rate by the number of unit-periods in a year". It is a common misconception that the APR calculation for loans is similar to the APY calculation for savings and investments (Truth in Savings Act). It is not. But even for the APY calculation, which is a compounded computation, the per-diem divisor is 365, not 360 -- although 366 is permitted to be used for leap years. ----- original message ----- "joel" wrote in message ... The 12 would be 12 months in a year and the 7 is a 7 year loan. The 6.5% is not correct if you are caculatting US interest. I used the formula =(1+(0.065/360))^30 which gave 1.005431 So you would use in the PMT formula .005432 065/12 = .005417 which isn't as accurate as the number from my formula. Either case you wouldn't get the same numbers as the bank gives you. the bank caculations where based on hand caculations made over a 100 years ago before computers and are the standards by law. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193845 http://www.thecodecage.com/forumz |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
I wrote:
"joel" wrote: I used the formula =(1+(0.065/360))^30 which gave 1.005431 [....] But even for the APY calculation, which is a compounded computation, the per-diem divisor is 365, not 360 I might add, however, that the practice of using 360 days per year and 30 days per month is not uncommon for yield calculations for some bonds. ----- original message ----- "Joe User" <joeu2004 wrote in message ... "joel" wrote: The 6.5% is not correct if you are caculatting US interest. I used the formula =(1+(0.065/360))^30 which gave 1.005431 So you would use in the PMT formula .005432 That is incorrect specifically for the US. You should review the Truth in Lending Act, notably appendix J. 6.5% was the given annual interest rate. The periodic interest is determined simply by dividing that by the number of periods per year. I assumed monthly, since Jeff did not specify. Even if the periodic unit were daily, the divisor would be 365, not 360. In any case, the periodic interest rate is never compounded to arrive at the APR. The APR is defined to be "the nominal annual percentage rate determined by multiplying the unit-period rate by the number of unit-periods in a year". It is a common misconception that the APR calculation for loans is similar to the APY calculation for savings and investments (Truth in Savings Act). It is not. But even for the APY calculation, which is a compounded computation, the per-diem divisor is 365, not 360 -- although 366 is permitted to be used for leap years. ----- original message ----- "joel" wrote in message ... The 12 would be 12 months in a year and the 7 is a 7 year loan. The 6.5% is not correct if you are caculatting US interest. I used the formula =(1+(0.065/360))^30 which gave 1.005431 So you would use in the PMT formula .005432 065/12 = .005417 which isn't as accurate as the number from my formula. Either case you wouldn't get the same numbers as the bank gives you. the bank caculations where based on hand caculations made over a 100 years ago before computers and are the standards by law. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193845 http://www.thecodecage.com/forumz |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
APR calculation for Mortgages with a balloon payment.
The truth in leading law simply states that all lenders have to give an "apr" rate for comparison purposes for the actual number of days in a year. The truth in leading law doesn't explain the math behind the calculations nor the laws and common practices on how to perform the calculations. Taking the arp rate and dividing by 365 no way gives the daily interest rate. If that was the case then I/365 = ((1 + I/365)**365) - 1 I have never seen any US banks give me a different interest rate for months that are different number of days nor loans that have different payments for diffferent length months. The monthly interest rates are all the same because of the 5 bank holidays. -- joel ------------------------------------------------------------------------ joel's Profile: 229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=193845 http://www.thecodecage.com/forumz |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Loan Schedule with Balloon Payment | Excel Worksheet Functions | |||
Can excel calculate a loan payment with a balloon at the end? | Excel Discussion (Misc queries) | |||
how do i create a loan with a balloon payment in Excel? | Excel Worksheet Functions | |||
Calcualte APRs for ARMs and Balloon payment mortgages | Excel Programming | |||
a template for loan imortization with balloon payment | Excel Discussion (Misc queries) |