Amortization of Prepaid Rent

[jdavis]

The project is to amortize the "Gross" rent over a period of days represented by "Lease Start" and "Lease End". The "Trans Date" is used to calculate the "First Mo." days. The formulas I have created in rows 4, 5, and 6, work fine when there is not a new month starting in the monthly schedule. However, in rows 7-10 those formulas do not work.

In rows 7-10 the portion of the first month is being calculated with a different formulas than the remaining months and the month following the last month of the amortization indicates #DIV/0!. Also note that the balance is not zero (AF7).

I need to have the same formula that works for all rows and months. Can someone show me the way?

Thanks

 

[Chihiro]

Line 8, following evaluate to 0 and thus you will get #Div/0 error.
IF($E8-DATEDIF($B8,V$2,"d")<=0,"0",SUM($E8-DATEDIF($B8,V$2,"d")))
(IE. IF(True,"0",Calc) = 0)

Same issue for all following lines as well.

I'd recommend using Evaluate Formula to step through your formula to identify section where it's causing error.

 

[jdavis]

Thanks, but that only identifies the #DIV/0 error. What about a formula for the first month of amortization which will also work for the duration of the "Days Amort" each month while looking at a decreasing balance? Note that J8 and K8 have 2 different formulas I would prefer to have just one that works for all months.

 

[Chihiro]

Here you go.

I changed things around a bit.

Daily: Used PMT to calculate payment
For F4: =ABS(PMT(0,C4-B4,D4))
Copy down.

Days Amort: Not needed
First Mo.: Not needed

Keep first column before start of calculation (F) as blank column (needed to make calculation dynamic).

For G4: =IF(G$2=$A4,(G$2>$B4)*(G$2-$B4)*$E4,IF(AND(G$2>$A4,G$2<=$C4),(G$2-$B4)*$E4-SUM($F4:F4),IF(AND(G$2>$A4,G$2>$C4),($C4-$B4)*$E4-SUM($F4:F4),0)))

Copy across and down.

Edit: Corrected typo

 

[jdavis]

I hate to be really dense on this one, but would you send back a file showing your columns? I go the daily payment calculated but am having a terrible time getting the column arrangement.

Thanks

PS: I finally got your formula to work down and across, but the year end balance "Balance 12/31/14" is gone and that is a must have column.

 

[Chihiro]

Simply subtract column H from Gross.

See attached.

 

[jdavis]

Simply enough, thanks! I have been a little braindead on this one.

Thanks again.