Hi,

Can someone please help to create a formula for a deferred revenue amortisation schedule?
The amortisation calculates by date and not month please.

I attached an example for working.

Thank you,

Haha

Attached Files

Deferred Revenue Amortisation Schedule.xlsx (9.1 KB, 19 views)

I am not quite sure what you want.
Could you please try to manually enter expected results in your file.

I re-attached the worksheet to illustrate what I'm looking for.
Thank you.

Attached Files

Deferred Revenue Amortisation Schedule.xlsx (9.3 KB, 19 views)

I am still not quite sure.
Maybe someone else can help.

Thank you for trying .

For me also remained unclear, what you want to calculate, but I tried to quess

Attached Files

AmortisationSchedule.xlsx (21.1 KB, 15 views)

Thank you.
What I really need is: month end = 28/02/17
1. In Feb column, the income is calculate from contract starts date to end of feb
2.if the contract is 12 mths, the next 10 mths = contract total value/12*1
3.column for month 12th = total contract value - total sum of all previous months

Will this be any more clear?

@haha

Please do not quote entire posts unnecessarily. They clutter the thread and make it hard to read.
Thank you

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

In sheet Hidden (or something like this, I don't have Excel at home) in file, updated by me, is a table where in second column are calculated month end dates for whole months range. Look there for formula!

Generally, a last day of month equals with 0th day of next month.

Btw, in my example I based all calculations on daily depreciation - so all calculations for any time interval are uniform. According your last post, you want depreciation for all full months to be same, and for non-full months based on days in period. There will be several problems with it.
1. You have to apply several different calculation rules;
2. You have to decide, do you count non-full start and end months as a single full month, or as separate non-full months;
3. In case you count non-full start and end months as single full month, you have to decide, which rules do apply, when the period starts and ends in February, and either start or end month belongs to leap year;
4. And add cases, where the period is several years (and not full years).

There is a reason why banks and leasing firms prefer to start all leasing periods at 1st of start month and end at last of end month!