This needs a genius!

[Kathy]

This question will take an experienced mind in Excell
2003.

In an amortization schedule, the beginning date changes
in the first row according to the 1st payment due date
entered. Each row after shows the next months payment P&I
breakdown for the months and years following, until the
loan is paid off.

Two Questions:

1. If I have several amortizations sheets, all with
different start dates, and I need a year end figure
pulled for every month in the year I am reporting (there
will be a new report for every year)is there formula I
can use in my report, since the same dates will be on a
different row in each shedule?

Is there a function that will look for an identical date
in column J and pull info from column K in the same row?

2. This same amortization schedule has a column for
Additional Payments.

In a separate part of the same worksheet the amortization
schedule is on, there are cells containing the amount of
John Doe's additional payment for $100.00 for January
2003 (Jan 2003 is on the same row, in the next column,as
the amount).

I need a formula that will find the corresponding date in
the amortization sheet and put John Doe's Additional
Payment amount in column next to, on the same row of the
corresponding date.

I can send a copy of the sheet which shows all that I'm
refering to. Thank you for any help you can be.

Miss Kathy

 

[ozzie]

Miss Kathy - it appears that you are trying to determine
the amount by month or year that was earned in interest,
the amount of additional prinicipal that was paid, the
balance of unpaid balances, etc. this sounds like a job
for a data base program (such as Access).

If someone does come up with an answer I would really,
really like to view the solutions since it interests me.

get me at ozzie_90680 at oohay . moc(SWITCH IT AROUND
after at)

 

[Harlan Grove]

This question will take an experienced mind in Excell
2003.

Or any other Excel version, or any spreadsheet for that matter. This is an old
problem.

In an amortization schedule, the beginning date changes
in the first row according to the 1st payment due date
entered. Each row after shows the next months payment P&I
breakdown for the months and years following, until the
loan is paid off.

Two Questions:

1. If I have several amortizations sheets, all with
different start dates, and I need a year end figure
pulled for every month in the year I am reporting (there
will be a new report for every year)is there formula I
can use in my report, since the same dates will be on a
different row in each shedule?

Is there a function that will look for an identical date
in column J and pull info from column K in the same row?

Not easily. It'd be far easier to calculate fiscal year subtotals with each FY
in the exact same location on each of the worksheets containing one of these
amortization schedules. Then have your summary worksheet total these subtotal
ranges. Otherwise, you'll need either an add-in function such as THREED in
Laurent Longre's MOREFUNC.XLL add-in (http://longre.free.fr/english/) or a
user-defined function to calculate conditional sums over ranges in several
worksheets.

2. This same amortization schedule has a column for
Additional Payments.

In a separate part of the same worksheet the amortization
schedule is on, there are cells containing the amount of
John Doe's additional payment for $100.00 for January
2003 (Jan 2003 is on the same row, in the next column,as
the amount).

...

Meaning the date is immediately to the right of the additional principal
payment? BAD DESIGN! Are these at least sorted in ascending date order? If so,
you could pull them using a simple LOOKUP or VLOOKUP call, e.g., for the payment
month in worksheet row 5 in the amortization schedule,

=IF(LOOKUP(A5,$Y$2:$Y$361)>=A5,LOOKUP(A5,$Y$2:$Y$361,$X$2:$X$361),0)

where A5 is the date from the amortization table, Y2:Y361 the column of dates of
additional principal payments, and X2:X361 are the additional principal payment
amounts.