a formula to show when costs should be recognised

**Jez Spiers** · 04-01-2012, 01:08 PM

Hi all

I hope someone can help me out with this headache! I have a sheet as follows

Column A Start Date
Column B End Date
Column C No of Months
Column D Contract Value
Columns E etc are months through to 36 months

I am buying service contracts of differing terms and need to show which months to recognise the costs. for example a start date of 04/04/2012 and end date of 30/09/2012 will show 0 in Jan, Feb & March but the pro-rated costs in April through September and then 0 in October, November etc

I am sure there must be a "simple" formula to do this and would very much appreciate any guidance.

Many thanks

Jez

**zbor** · 04-01-2012, 01:19 PM

How do you mean Columns E to ... are months up to 36 months? What's in their headers?

Hint: Fastesd solution would be to upload example workbook...

**Ron Coderre** · 04-01-2012, 01:34 PM

I use an amortization template (see attached).
Does that help?

**Jez Spiers** · 04-02-2012, 04:06 AM

Hi zbor & Ron

please see the attached example, in very simple terms. Ron, your template looks like it might be very useful

I'll have a play around with it and see if it helps - and ther reverse engineer it to make sure I underdstand HOW it is doing what it is doing! Many thanks from an Excel simpleton