I would like to be able to enter varying interest rates throughout the life
of a mortgage... I need to display the historical data i.e. paid in
principal to-date, paid in interest to-date, etc.

On Jul 28, 5:16 pm, jack in need of assistance <jack in need of
wrote:
I would like to be able to enter varying interest rates throughout the
life of a mortgage... I need to display the historical data i.e. paid in
principal to-date, paid in interest to-date, etc.

So what exactly is your question: (a) how to design such an
amortization schedule from scratch; or (b) how to modify an existing
(MS) template? Or is your question something else altogether?

If you know the mechanics of designing an amortization schedule,
varying the interest rate is really not difficult. The requirements
you describe above are simple running totals. Do you perhaps need
help in, for example, how you would recompute the payment?

Well, I'm fishing. Happy to help. But please be more specific.

Thanks for your interest... I am not skilled with excel. Whether I modify a
template or simply build it from scratch, or pay for help, the easier the
better...

I would like to be able to adjust a 30 year loan with payments monthly. I
need for the interest rate to accept adjustment during any period. It would
be like establishing a new loan at each point an interest change would be
made. The new beginning principal amount, however would be whatever the
outstanding balance is at the point of change. I realize that there will
likely be either an outstanding principal balance or an early pay off.

The duration of the loan from each point of change would remain the same end
date as the initial 30 year loan. Interest and principal per month plus
cumlative columns for interest, principal, etc. would be tabulated from
period 1 through period 360.

A typical design for the amortization schedule would be desirable. However,
an area that would display the interest rate on any given month, an average
interest rate to-date, and the variance of the rate from the starting rate
would be necessary.

In addition to the cumlative data, monthly data should be displayed as well.

thank you very much for your time and interest...
------------------

"joeu2004" wrote:

On Jul 28, 5:16 pm, jack in need of assistance <jack in need of
wrote:
I would like to be able to enter varying interest rates throughout the
life of a mortgage... I need to display the historical data i.e. paid in
principal to-date, paid in interest to-date, etc.

So what exactly is your question: (a) how to design such an
amortization schedule from scratch; or (b) how to modify an existing
(MS) template? Or is your question something else altogether?

If you know the mechanics of designing an amortization schedule,
varying the interest rate is really not difficult. The requirements
you describe above are simple running totals. Do you perhaps need
help in, for example, how you would recompute the payment?

Well, I'm fishing. Happy to help. But please be more specific.

On Jul 28, 10:56 pm, jack in need of assistance
m wrote:
Thanks for your interest... I am not skilled with excel. Whether I
modify a template or simply build it from scratch, or pay for help,
the easier the better.

If you are a lender or this is otherwise for professional purposes, it
would be prudent to pay someone close at hand; that is, someone who
would be financially responsible for the job. (Not me.)

You could also look at the loan templates provided by Microsoft Office
Online. I do not believe any of them do what you want exactly; and I
quibble with the details of the some of them (e.g. failing to round
the computed payment amount). But they might be a starting point; at
the very least, they might give you some idea of how an amortization
schedule should look.

I would like to be able to adjust a 30 year loan with payments monthly. I
need for the interest rate to accept adjustment during any period. [....]
Interest and principal per month plus cumlative columns for interest,
principal, etc. would be tabulated from period 1 through period 360.

Assuming that this is for a US loan in which interest is computed
monthly, the following might get you started. Put the following
titles in row 1:

A1: Pmt Number
B1: Due Date
C1: Interest Rate
D1: Payment
E1: Interest
F1: Principal
G1: Balance
H1: Cum Interest
I1: Cum Principal
J1: Cum Payment

Use row 2 to record the initial loan amount in G2.

Use row 3 to record the terms of the first payment, namely (caveat:
untested):

A3: 1
B3: 8/1/2007 [substitute the real due date of the first payment]
C3: 6% [substitute the real annual interest rate]
D3: =if(or(A3=360, roundup(G2+E3,2) < roundup(pmt(C3/12,360-A3+1,-G2),
2)), roundup(G2+E3,2), roundup(pmt(C3/12,360-A3+1,-G2),2))
E3: =G2*C3/12 [see Notes below]
F3: =min(G2, D3-E3)
G3: =max(0, G2+E3-D3)
H3: =H2+E3
I3: =I2+F3
J3: =J2+D3

Use row 4 to set up the paradigm for the remainder of the schedule,
namely:

A4: =A3+1
B4: =date(year(A3), 1+month(A3), day(A3))
C4: =C2
D4:J4: copy-and-paste or drag down D3:J3

Copy-and-paste or drag A4:J4 down through A362:J362.

Then, all you need to do is modify the cells in column C to reflect
the adjusted interest rate.

Notes:

1. Technically, it might be better to compute round(pmt(...),2) just
once in a helper column, which you can hide. Also, there are other
improvements that would minimize computation. But IMHO, the
spreadsheet is small enough that you will probably not notice the
inefficiencies.

2. The computation in E3 might need improvement. First, rounding is
debatable. Second, in any case, the formula might need to be improved
to account for rounding up the last payment; any amount in excess of
the balance should be considered interest. But we should be talking
about only a penny.

3. Some of the formulas could be embellished to improve presentation
in the case where the loan is paid off early due to rounding up the
payment. In that case, it might be nice if all columns were blank.

A typical design for the amortization schedule would be desirable. However,
an area that would display the interest rate on any given month, an average
interest rate to-date, and the variance of the rate from the starting rate
would be necessary.

In addition to the cumlative data, monthly data should be displayed as well.

Those embellishments could be incorporated fairly easily; it is only a
question of aesthetics. I don't see the value of "monthly data"; that
is exactly what the amortization schedule shows. I wonder if you mean
__yearly__ data.

Hope this helps.

Errata....

On Jul 29, 1:50 am, I wrote:
C4: =C2

Obvious typo: that should be =C3, not =C2.


B4: =date(year(A3), 1+month(A3), day(A3))

If you installed the Analysis ToolPak, a more reliable formula would
be:

B4: =date(year(A3), 1+month(A3), if(or(day($A$3)=eomonth($A$3,0)),
day($A$3)eomonth(A3,1)), eomonth(A3,1), day($A$3)))

Thus, if the first payment is at the end of the month (e.g. Feb 28),
every payment will be at the end of its respective month (e.g. Mar 31
and Apr 30 instead of Mar 28 and Apr 28). The same is true if the
first payment is on the 29th or 30th of the month and the current
month (Feb) has fewer days.

Alternatively, you can get the same result simply by filling in the
first 2 or 3 dates, selecting those cells and dragging down through
row 362. Excel tries to interpret the pattern.


D3: =if(or(A3=360, roundup(G2+E3,2) < roundup(pmt(C3/12,360-A3+1,-G2),
2)), roundup(G2+E3,2), roundup(pmt(C3/12,360-A3+1,-G2),2))

This is a nitpick, but: on second thought, I prefer to remove the
first "roundup" call, thus:

D3: =if(or(A3=360, G2+E3 < roundup(pmt(C3/12,360-A3+1,-G2),2)),
roundup(G2+E3,2), roundup(pmt(C3/12,360-A3+1,-G2),2))