[SOLVED] Amitorization schedule?

[Cherlosheart]

How do I set up an Amitorization schedule/chart in Excel?

[MWE]

check out the PV )Present Value) function and the "See Also" functions. It should help you set up the necessary tables.

[Duke Carey]

check this MS article

http://support.microsoft.com/default...b;en-us;816643

"Cherlosheart" wrote:

> How do I set up an Amitorization schedule/chart in Excel?

[rlockwood]

How do I set up an Amitorization schedule/chart in Excel? For al Functions of P, A, i, r, etc..

[Brian]

There is an Amortization template in Excel 2003 that works great.





"rlockwood" <[email protected]> wrote in
message news:[email protected]...
>
> How do I set up an Amitorization schedule/chart in Excel? For al
> Functions of P, A, i, r, etc..
>
>
> --
> rlockwood
> ------------------------------------------------------------------------
> rlockwood's Profile:
http://www.excelforum.com/member.php...o&userid=29455
> View this thread: http://www.excelforum.com/showthread...hreadid=359985
>

[Fred Smith]

An amortization schedule is pretty simple. The columns are Period, Opening
Balance, Payment, Interest, Closing Balance

Opening Balance is Closing Balance of previous row, or the starting amount of
the loan.
Interest = OpeningBalance * IntRate / NumPmtsPerYear
Closing Balance = OpeningBalance - Payment + Interest

Copy down until Closing Balance gets to zero.

I don't know what you mean by "al functions of P, A, i, r". Excel financial
functions do a lot more than just amortization tables. If what you want to know
are the formulas, they are in Help.

--
Regards,
Fred


"rlockwood" <[email protected]> wrote in
message news:[email protected]...
>
> How do I set up an Amitorization schedule/chart in Excel? For al
> Functions of P, A, i, r, etc..
>
>
> --
> rlockwood
> ------------------------------------------------------------------------
> rlockwood's Profile:
> http://www.excelforum.com/member.php...o&userid=29455
> View this thread: http://www.excelforum.com/showthread...hreadid=359985
>

[[email protected]]

"rlockwood" wrote:
> How do I set up an Amitorization schedule/chart in Excel?
> For al Functions of P, A, i, r, etc..

There is a l-o-t more information that you need to give in
order to answer the question properly. For example, what
loan parameters do you have to begin with? What frequency
do you want the schedule to reflect; for example, monthly
(per payment) or annually?

Typically, we have at least the loan amount (pv), the nominal
annual interest rate (i), and the term of the loan in months
(n). We assume monthly payments, and we assume interest
s compounded monthly. In that case, the payments are:

ROUNDUP(PMT(i/12, n,, -pv), 2)

A monthly amortization table might consist of the following
columns and formulas:

A = payment date (useful when compounding daily)
B = payment: ROUND(PMT(i/12, n,, -pv)
C = interest: C2 = B1*(i/12)
D = principal: D2 = B2 - C2
E = Balance; E1 = loan amount; E2 = E1 - D2

Note-1: Last payment (B360 for a 30-year loan) =
D359 + C360; i.e, the remaining balance plus interest.
This may be larger or smaller than the regular payment.

Note-2: I do not know whether or not lenders round
the formula in column C (interest).

Things get more complicated if you have the "APR", but
not the nominal interest rate, and if interest is compounded
daily. If you have the "APR", it is important to to know
whether it is the Reg Z APR or an advertised APR. They
are computed very differently. If you have the Reg Z
statement, the payment should be taken from there, not
computed as above. Morevoer, the above assumes a US
loan or similar. Other countries might do things differently.