Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 905
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 905
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 2
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 905
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 905
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 905
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Loan Schedule with Balloon Payment R0bert Neville Excel Worksheet Functions 7 April 22nd 23 12:08 PM
Can excel calculate a loan payment with a balloon at the end? Darren @ Vermeer Excel Discussion (Misc queries) 2 December 15th 08 04:04 AM
how do i create a loan with a balloon payment in Excel? Rox A. Excel Worksheet Functions 1 November 18th 06 12:16 AM
Calcualte APRs for ARMs and Balloon payment mortgages Confused in Chicago Excel Programming 0 September 2nd 05 07:55 PM
a template for loan imortization with balloon payment stevep Excel Discussion (Misc queries) 1 May 8th 05 07:20 PM


All times are GMT +1. The time now is 11:23 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"