Splitting Values Accross Multiple Years

[Ravi Gokal]

How do you split an amount across multiple years for example

Start Date 24/10/2013
End Date 30/11/2018
Amount $ 20,0000

FY 2013
FY 2014
FY 2015
FY 2016
FY 2017
FY 2018

The financial year begins on 1 July and ends on 30 June

What is would be the excel formula to solve this?

 

[SirJB7]

Hi, Ravi Gokal!

As a new user you might want (I'd say should and must) read this:
http://chandoo.org/forum/forums/new-users-please-start-here.14/

And regarding your issue, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you.

Particularly I'm a bit confused on the base period for distribution (Y, Q, M).

Just as a blind shot, give a look at the uploaded file. In B4 enter the periodicity in months and check if that's what you're looking for.

Formulas:
D1: =SI(FECHA(AÑO($B1);MES($B1)+$B$4*(COLUMNA()-3);DIA($B1))<=$B2;FECHA(AÑO($B1);MES($B1)+$B$4*(COLUMNA()-3);DIA($B1));"") -----> in english: =IF(DATE(YEAR($B1),MONTH($B1)+$B$4*(COLUMN()-3),DAY($B1))<=$B2,DATE(YEAR($B1),MONTH($B1)+$B$4*(COLUMN()-3),DAY($B1)),"")
D2: =SI(D1<>"";$B$3/(AÑO($B$2)*12+MES($B$2)-AÑO($B$1)*12+MES($B$1)/$B$4);"") -----> in english: =IF(D1<>"",$B$3/(YEAR($B$2)*12+MONTH($B$2)-YEAR($B$1)*12+MONTH($B$1)/$B$4),"")
Copy across as required. Yellow shaded just to inform up to which column are formulas copied.

Regards!

 

[Ravi Gokal]

Hi Sir JB7,

Thank you for your reply.

Sorry I should have been more clear in my intent.

For example I have made a $20,000 saving acorss the life of a contract which starts on

24/10/2013 and ends 30/11/2018. So I need to split that $20,000 across the different financial years. A financial year starts on 1 July and ends on 30 June.


I have attached a worked example.

Thank you again.

 

[r1c1]

I think a better way to split would be to calculate days per financial year (rather than months). This gives accurate split. See attached for a solution.

The basic approach is like this:

1. For each financial year, calculate how many days need to be considered.
   
2. Then divide this days with the total days in the splitting window to get that financial year's cut.

Examine the formulas in the file to understand how this is done.

 

[kchiba]

Hi Ravi,

There is an error in your calculations, please check the attached, you will note that I have used some helper rows and cells

kanti

 

[kchiba]

Hi Ravi,

As the problem is very similar to a depreciation calculation you can use the following function

AMORLINC()

cheers

kanti

 

[Ravi Gokal]

I really want to us the depreciation function. But what rate would you use?

 

[Ravi Gokal]

Sorry I just understood the formula. Had a small brain freeze.

Thank you again guys for all your help.

 

[kchiba]

No problem, glad it worked for you.

This is a very useful function that can be used for a number of purposes especially in Financial Modelling.

You can spread anything you want across periods like loan repayments, costs revenues.