Function to calculate the interest of a mortgage

G

Grd

Hi,

How can I do an amortization schedule that gives me the interest as its
being paid off?

Is there a function that can help.

Thanks in Advance
Any help greatly appreciated

Regards
Janet
 
N

Niek Otten

Hi Janet,

There is a family of financial functions that address this and similar
problems: RATE(), NPV(), FV(), PMT(), NPER() etc.
In Analysis Toolpak (Tools>Add-ins, check Analysis Toolpak if not done
already) there is a family for irregular payments.
See HELP for details; post again in this thread if you still have problems.
 
B

B. R.Ramachandran

Hi,

Use IPMT function. If you don't have the toolpack installed, you can use
the following formula:

The interest due in the Nth month is,

L*R*(1+R/1200)^(N-1) - P*((1+R/1200)^(N-1)-1)

where L is the initial loan amount, R is the annual percent interest rate,
and P is the monthly payment made. When you enter the formula in Excel,
reference the cells containing the appropriate information for L, R, N, and P
(or hardwire their values), and remember to start the formula with an "="
sign.

Regards,
B. R. Ramachandran
 
J

joeu2004

Grd said:
How can I do an amortization schedule that gives
me the interest as its being paid off?
Is there a function that can help.
Click to expand...

You are asking two very different questions. Some people
have tried to answer the second question. The first question
cannot be answered completely because you fail to state the
frequency of the amortization that you want in your table --
for example, per payment or annually.

In any case, you might want to look at Mortgage Amortization
template. It might provide you with some good ideas, if it is
not in fact exactly what you are looking for.

One way to find the Mortgate Amortization template ....

First, be sure that your Help search mode is set for online by
doing the following. Click Help and Microsoft Help, then click
Online Content Settings under See Also. If Show Content
from Microsoft Office Online is not check-marked, click to add
a check-mark, click Okay and exit and restart Excel.

Then, click Help and Microsoft Help, then click the Excel Help
toolbar and click Search Results. Under Search, select
Template from the pull-down list, type "mortgage" in the
search window, and press Enter. Scroll through the search
results and select Mortgage Amortization Schedule.
 
G

Grd

Thanks

The IPMT function looks like the candidate. I will experiment with it.

Janet
 
G

Grd

Thanks for pointing me in the right direction

J

Niek Otten said:
Hi Janet,

There is a family of financial functions that address this and similar
problems: RATE(), NPV(), FV(), PMT(), NPER() etc.
In Analysis Toolpak (Tools>Add-ins, check Analysis Toolpak if not done
already) there is a family for irregular payments.
See HELP for details; post again in this thread if you still have problems.
Click to expand...
 
V

vandenberg p

Actually none of the functions will produce an amortization schedule.
They can be used to produce one, but the easy way is to download one
from here:

http://office.microsoft.com/en-us/templates/TC010197771033.aspx?CategoryID=CT011377171033

Pieter Vandenberg


: Thanks for pointing me in the right direction

: J

: "Niek Otten" wrote:

:> Hi Janet,
:>
:> There is a family of financial functions that address this and similar
:> problems: RATE(), NPV(), FV(), PMT(), NPER() etc.
:> In Analysis Toolpak (Tools>Add-ins, check Analysis Toolpak if not done
:> already) there is a family for irregular payments.
:> See HELP for details; post again in this thread if you still have problems.
:>
:> --
:> Kind regards,
:>
:> Niek Otten
:>
:> :> > Hi,
:> >
:> > How can I do an amortization schedule that gives me the interest as its
:> > being paid off?
:> >
:> > Is there a function that can help.
:> >
:> > Thanks in Advance
:> > Any help greatly appreciated
:> >
:> > Regards
:> > Janet
:>
:>
:>
 
Top