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