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,
additional principal payment, etc.
 
Ad

Advertisements

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,
additional principal payment, etc.
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
additional principal will be added to this amount.
--


Rick Hess
New Orleans
To reply, eliminate All_Spammers
 
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
additional principal will be added to this amount.
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.
Email me with your real addy with "Send Amortization Spreadsheet" for the
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
To reply, eliminate All_Spammers
 
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.
 
Ad

Advertisements

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
To reply, eliminate All_Spammers
 
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
 
Ad

Advertisements

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
To reply, eliminate All_Spammers
 
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
To reply, eliminate All_Spammers
 
Ad

Advertisements

T

Tom Putnam

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

Tom
 
Ad

Advertisements

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...
 
Ad

Advertisements


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

Similar Threads

Amortization formula for daily interest 5
Formula = ? 3
AMORTIZATION 1
Amortization 1
amortize 8
payroll formula 1
Excel formula 0
Alias Formula 2

Top