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!

"Jeff" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:647226b8-cb8c-493b-b968-(E-Mail Removed)...
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!

I wrote:
> "Jeff" <(E-Mail Removed)> 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
news:(E-Mail Removed)...
> "Jeff" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> news:647226b8-cb8c-493b-b968-(E-Mail Removed)...
> 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!

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" <mickelso...@gmail.com> 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
>
> news:(E-Mail Removed)...
>
>
>
> > "Jeff" <mickelso...@gmail.com> 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" <mickelso...@gmail.com> wrote in message
> >news:647226b8-cb8c-493b-b968-(E-Mail Removed)...
> > 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!

"Jeff" <(E-Mail Removed)> 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" <mickelso...@gmail.com> 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
>
> news:(E-Mail Removed)...
>
>
>
> > "Jeff" <mickelso...@gmail.com> 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" <mickelso...@gmail.com> wrote in message
> >news:647226b8-cb8c-493b-b968-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>

I wrote:
> "joel" <(E-Mail Removed)> 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
news:(E-Mail Removed)...
> "joel" <(E-Mail Removed)> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>>
>> 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
>>
>