Need an Excel formula to prorate invoices

Microsoft Office excel 2003 - upgrade
May 11, 2011 at 11:42:01
Specs: Win XP SP4, Intel Core Duo 3.8Gz // 4GB RAM
I have a bunch of invoices that I need to calculate a revenue deferral on, and need a formula to do so. I've been experimenting, but finding it quite complicated.

The periods I need to prorate the invoices into (column headers) are as follows:
1/1/09-12/31/09
1/1/10-12/31/10
1/1/11-3/31/11 (note: chg'd yr end; so shortened yr)
4/1/11-3/31/12
4/1/12-3/31/13 etc.

Ideally, I'd like them rounded to half month periods (for cleaner rounding and calcs).

Here are examples of what I need Excel to do:

Inv # 1 -- $12k -- starts 1/1/09, ends 12/31/09.
S/B $12k to 2009, $0 to 2010, $0 to 2011
[All 12 mos in 2009]

Inv # 2 -- $12k -- starts 7/1/09, ends 6/30/10.
S/B $6k to 2009, $6k to 2010, $0 to 2011
[6 mos in 2009 = 6/12*12000, same for 2010]

Inv # 3 -- $12k -- starts 9/1/09, ends 8/31/10
S/B $4k to 2009, $8k to 2010, $0 to 2011
[4 mos in 2009 = 4/12*12000; 8 mos in 2010 = 8/12*12000]

Inv # 4 -- $12k -- starts 9/15/09, ends 9/14/10
S/B $3500 to 2009, $8500 to 2010, $0 to 2011
[3.5 mos in 2009 = 3.5/12*12000; 8.5 mos in 2010 = 8.5/12*12000]

Inv # 5 -- $12k -- starts 1/15/10, ends 1/14/11
S/B $0 - 2009, $11,500 - 2010, $500 - 2011
[11.5 mos in 2010 = 11.5/12*12000; .5 mos in 2011 = .5/12*12000]

Inv # 6 -- $12k -- starts 8/1/10, ends 7/30/11
S/B $5k to 2010, $3k to 2011, $4k to 2011-12
[5 mos in 2010 = 5/12*12000; 3 mos in 2011 = 3/12*12000; 4 mos in 2011-12 = 4/12*12000

Hope this makes sense.

Can anyone help??


See More: Need an Excel formula to prorate invoices

Report •


#1
May 13, 2011 at 04:56:22
Please do not duplicate your questions.

If someone has an answer, they will post it.

Your most recent duplicate post has been deleted.

DerbyDad03
Office Forum Moderator

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
May 13, 2011 at 15:36:44
Apologies DerbyDad - I intended to re-post with a different category heading (rather than "Office Excel 2003 - Upgrade").

This said, I saw on this forum that you were able to provide a solution to a request similar to mine.

Any chance you could help me figure this one out...?

Not sure if you're able to take payment for this...but I would be happy to send you and a friend/significant other for dinner or something (wherever you are) if you can solve this for me...

I already created a formula that works (a few years back) - but it's very long and very complex and only does an annual pro ration (i.e.: it doesn't address the change in year end as described above).

I could send the existing spreadsheet...

Let me know your thoughts...and thanks for your consideration...

Troy


Report •

Related Solutions


Ask Question