Stumped

[dcd123]

Is there a way to do the following:

I have an annual $ amount that appears on another sheet that I want to
split into 12 payments, each payment listed in a different cell on the
one sheet (there are about 50 annual amounts that I need to do this
for, meaning I have a monthly summary of all monthly payments).

In some cases, the annual amount will change in the middle of the year,
therefore changing the monthly payment at that time as well. If I
simply divide the annual amount by the 12 months, ALL amounts will
change. I want the amounts prior to the change to remain as they were
with the original amount and the remaining payments to reflect the
original amount +/- the difference for the remaining months.

I hope this makes sense and is possible without manually entering in
each monthly payment.

Thanks for any help!

 

[Bucky]

In some cases, the annual amount will change in the middle of the year,
therefore changing the monthly payment at that time as well. If I
simply divide the annual amount by the 12 months, ALL amounts will
change. I want the amounts prior to the change to remain as they were
with the original amount and the remaining payments to reflect the
original amount +/- the difference for the remaining months.

Wow, that's pretty tough. That's not really possible unless you keep a
history of annual amounts. Otherwise, as soon as you change the annual
amount, you lose the old value, so how can they reference a value that
doesn't exist anymore? The only ways I can think of are:

1. Write some complicated macro. Not really worth it.

2. Instead of one cell for the annual amount, use 12 cells, one for
each month.

3. Do it manually. If the monthly payments are all in adjacent cells,
this is very easy. Let's say you're changing the annual amount in July.
Select all the monthly payments from Jan to June. Copy. Paste Special >
Values. You will have to adjust/compensate the annual amount to have
the remaining months work out right.

 

[Harlan Grove]

dcd123 wrote...

I have an annual $ amount that appears on another sheet that I want to
split into 12 payments, each payment listed in a different cell on the
one sheet (there are about 50 annual amounts that I need to do this
for, meaning I have a monthly summary of all monthly payments).

In some cases, the annual amount will change in the middle of the year,
therefore changing the monthly payment at that time as well. If I
simply divide the annual amount by the 12 months, ALL amounts will
change. I want the amounts prior to the change to remain as they were
with the original amount and the remaining payments to reflect the
original amount +/- the difference for the remaining months.

....

You'd need to enter annual amounts twice anyway - initial estimates and
6 month revisions - so enter them in separate cells. For illustration,
I'll put the initial estimate in cell B3 and the 6 month revision (of
the total annual payment) in cell B4. Then payments in month 1..6 are
each

=$B$3/12

and the payments in month 7..12 are each

=$B$3/12+($B$4-$B$3)/6