Prepaid Expense Amortization Formula

[Tracy2573]

I have the following columns in my spreadsheet and would like to insert a formula that will automatically update all of the applicable months with the correct amount.

Vendor Name

Description

Amount

Date Paid

Start

End

16-Jan

16-Feb

16-Mar

16-Apr

16-May

16-Jun

16-Jul

16-Aug

16-Sep

16-Oct

16-Nov

16-Dec

Test

Data

$ 12,000.00

2/25/2016

16-Jun

16-Nov

<colgroup><col><col><col><col><col><col><col span="12"></colgroup><tbody>
</tbody>

Example: It needs to start in June and end on Nov but I want the formula to go in all columns so I can copy it down for the next prepaid item which may start in January.

Thank you,
Tracy

 

[Noodleski]

Assuming the titles in your example above start from A1, so this formula goes into cell G2. After input, copied to the right and down.

IF(OR(G$1<$E2,G$1>$F2),0,$C2/(MONTH($F2)-MONTH($E2)+1))

 

[Tracy2573]

Thanks, but if I try this in a test spreadsheet it seems to work fine if I format my column headings using d/m/yy, and the same in the start and end date columns. But when I enter this formula into my actual spreadsheet and format the columns the same I am getting the error message #DIV/0! starting in the month it should begin. I do have a few more columns in my actual spreadsheet but I have updated the correct columns in the formula.

My columns start at A1 and go to U1. My required data are in the following: E=amount, F=start date, and G=end date, and J=January, etc, all the way to December. I would post a picture of it but I am not sure how to copy a couple of rows and columns and paste it here.

Does it matter how the cells are formatted when it comes to the date and dollar? I have tried it several ways.

 

[Noodleski]

Hm, probably a difference in column references, as your OP seemed to indicate that your start date was in column E and end date in column F, but your new explanation seems to indicate other columns.
From what you now told me, the formula will probably need some adjustment.
You're best placed to check this, so make sure that where i now mention F2 you pick up the start date and where I mention G2, you pick up the end date.
I took a shot, based on the last info:

IF(OR(J$1<$F2,J$1>$G2),0,$C2/(MONTH($G2)-MONTH($F2)+1))

 

[Tracy2573]

I am still having issues, it is not dividing the amounts correctly. Is there any way I can send you an example of my spreadsheet?

 

[Tracy2573]

Oh, I finally got the formula to work. I had to format the start date as the first day of the month and the end date as the last day of the month because we always do them at month end. Thank you so much for your help with this.

 

[Tracy2573]

Sorry, one more question with this template, how would you adjust the formula if the term was for 3 years and it only happened once a year? For example every May?