APR calculation for Mortgages with a balloon payment.

[Jeff]

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!

 

[Joe User]

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 -----

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!

 

[Joe User]

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 -----

 

[Jeff]

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?

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

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)

However, my result is only about 6.61%.

----- original 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!

 

[Joe User]

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 -----

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

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)

However, my result is only about 6.61%.

----- original 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!

 

[joel]

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.

 

[Joe User]

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 -----

 

[Joe User]

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 -----

 

[joel]

The truth in leading law simply states that all lenders have to give a
"apr" rate for comparison purposes for the actual number of days in
year. The truth in leading law doesn't explain the math behind th
calculations nor the laws and common practices on how to perform th
calculations. Taking the arp rate and dividing by 365 no way gives th
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 fo
months that are different number of days nor loans that have differen
payments for diffferent length months. The monthly interest rates ar
all the same because of the 5 bank holidays.