prepaid/depreciation formulas

[markx]

Hello,

In row 6 of my worksheet I have dates corresponding to different months
(01.01.2006 for Jan 2006, 01.02.2006 for February 2006)
In columns A-H there are different infos regarding asset to be depreciated
(or prepaid expense), the most relevant being:
C: Starting Month (f. ex. 01.03.2006 for March 2006)
D: Ending Month (f. ex. 01.12.2008 for August 2008)
E: Number of Months during which the depreciation/prepaid should be executed
(f. ex. 12, special formula calculates this based on start/end months)
F: Total (Initial) Amount

Then, from the column "I" to all others (extended to the right), I put the
following formula (for row 8):
=IF(AND(I$6>=$C8;I$6<=$D8;I$6<>"");ROUND($F8*$H8/$E8;2);"")
which calculates me the amount to be prepaid/depreciated.

It's almost OK, the only problem being that during the last month to be
depreciated/prepaid the total amount risks to turn shightly to the negative
(due to ROUND)
F. ex. if the initial amount to be prepaid is 2065.86, to be subdivised to
12 payment periods (months), it will give me 172.16 per month, but the last
month should be 172.10 in order to get exactly 2065.86.

Do you have any idea how to modify the formula in order to fix this type of
problems?
Many thanks for your help on this!

Mark

 

[Marcelo]

Hi Mark,

I'm not sure if it is the right solution for your problem, but, is one way,
try it on the LAST month formula:

"=if(((round((a8/12;2)*12)>a8);round((a8/12);2)-((round((a8/12);2)*12)-a8);round((a8/12);2)+((round((a8/12);2)*12)-a8))"

I hope it helps you
regards
Marcelo


"markx" escreveu: