# Amortization Formula

T

#### The Wolf

Is it possible to import the Quicken loan calculator into an Excel sheet?

Or does anyone know the formula to compute payments for a 30 year loan at 7%

I want to have each payment in an Excel sheet. Principle, interest,

R

#### Rick Hess

The Wolf said:
Or does anyone know the formula to compute payments for a 30 year loan at 7%

I want to have each payment in an Excel sheet. Principle, interest,
In Excel, look up the PMT function.

You don't say what the Principal is, but for \$100K and assuming fixed
interest and no balloon the formula would be:

=PMT(7%/12, 360, 100000, 0, 0)

Of course this will only calculate the P&I. Your escrow impound and
--

Rick Hess
New Orleans

T

#### The Wolf

In Excel, look up the PMT function.

You don't say what the Principal is, but for \$100K and assuming fixed
interest and no balloon the formula would be:

=PMT(7%/12, 360, 100000, 0, 0)

Of course this will only calculate the P&I. Your escrow impound and
Thanks, I didn't know about that function.

That just gives the payment amount?

I would like to do something like the loan calculator in Quicken where you
can payment schedule and see each month for the entire loan what the
principal and interest amounts are.

Quicken doesn't have an export feature for this. Can I do this in Excel?

R

#### Rick Hess

The Wolf said:
I would like to do something like the loan calculator in Quicken where you
can payment schedule and see each month for the entire loan what the
principal and interest amounts are.

Quicken doesn't have an export feature for this. Can I do this in Excel?
Yes. You can use Excel's ISPMT function. That calculates the interest for
each period. You subtract that number from the P&I total to get P. Then
subtract P from the loan balance. Then you can plug the new loan balance
into ISPMT for the next line.

But I have the impression that you don't want to code this yourself. If
that's the case, and you want a pre-written spreadsheet, I have a few.
subject and I'll send you one. If you don't want to wait, just search a bit
and you'll probably come up with a few dozen easily.
--

Rick Hess
New Orleans

F

#### Fred Smith

Amortization tables are easy. You need four columns: Opening balance,
Payment, Interest, Closing balance.

On row 1, Opening balance is the amount of the loan; on subsequent rows it
the previous Closing balance.
Payment is PMT(...) amount as Rick showed you.
Interest is Opening * Interest Rate / #periods in year
Closing is Opening - Payment + Interest

G

#### GSalisbury

I use Q2003.
I went to Property&Debt/Loans and opened an existing Loan.
Selected the Payment Schedule Tab and Print.
That offered a number of output options: Ascii, Tabbed, 123 ... any one of
which could then be imported in Excel.
Try that.

R

#### Rick Hess

Rick Hess said:
You can use Excel's ISPMT function.
That should have read IPMT. I'm revealing my old Lotus days!

I emailed you several Excel worksheets. I don't remember what does what,
but if you don't see what you need, I have more, or -- as Fred, who's
response was clearer than mine, said -- it's not hard to write your own.
--

Rick Hess
New Orleans

T

#### The Wolf

I use Q2003.
I went to Property&Debt/Loans and opened an existing Loan.
Selected the Payment Schedule Tab and Print.
That offered a number of output options: Ascii, Tabbed, 123 ... any one of
which could then be imported in Excel.
Try that.
I have 2002 for Mac and don't see that option.

It would be worth upgrading if 2004 for Mac can do that.

T

#### Tom Putnam

<<...I'm revealing my old Lotus days!...>>

When Borland was developing Quattro Pro they code named the project Buddha
(I Beta tested the first 2 versions and learned this little factoid
somewhere along the way). Now for the \$64 question, why did they call the
project Budda?

Tom

F

#### FranksPlace2

Because it was BUDget DAta?

A

#### Andrew

FranksPlace2 said:
Because it was BUDget DAta?
Too assume the Lotus position.

R

#### Rick Hess

Tom Putnam said:
Now for the \$64 question, why did they call the
project Budda?

Hey, I'm gonna need a bit more than \$64 to research that!
--

Rick Hess
New Orleans

A

#### Andrew

Rick said:
Hey, I'm gonna need a bit more than \$64 to research that!
Nope = too easy - see my 802 PM post for the answer.

R

#### Rick Hess

Andrew said:
Nope = too easy - see my 802 PM post for the answer.

OK you won. Does that mean you're taking us out to lunch with the winnings?
--

Rick Hess
New Orleans

A

#### Andrew

Rick said:
OK you won. Does that mean you're taking us out to lunch with the
winnings?
You know, I would. Next time I'm in New Orleans for sure. But I confess,
with a simple Google search of "Quattro Pro Buddha", it took me all of 20
seconds to find the answer. The returned reference, fyi, was
taken the fun out of trivia!

T

#### Tom Putnam

Ah, give the man the prize! How'd you know it?

Tom

T

#### Tom Putnam

Good guess, but Andrew got the correct answer. Darn, figured I'd stump
everybody. <sigh>

Tom

T

#### Tom Putnam

You live in God's country, that's reward enough! <g>

Tom

T

#### Tom Putnam

Oh, so THAT'S the way you did it. Gee, and here I was impressed by your
speed and skill. I shoulda known...