APR calculation for Mortgages with a balloon payment.

J

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

Joe User

Jeff said:
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!
 
J

Joe User

I said:
Jeff said:
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 -----
 
J

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?

I said:
Jeff said:
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!
 
J

Joe User

Jeff said:
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 -----

I said:
Jeff said:
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!
 
J

Joe User

joel said:
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 -----
 
J

Joe User

I said:
joel said:
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 -----
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top