Depreciation calculation question

[exce101]

Hi All,

I am struggling with a nested IF formula to calculate monthly depreciation (exact to the number of days) based on the following fields

- Date asset is placed in service (column AH)
- End of life of the asset (column A)

I also have some other checks i.e. in column D checking is it a new asset in the current financial year (FY24 = Oct'23 to Sep'24) and whether the asset will reach full life in the current financial year (column F)

I want to be able to calculate accurate depreciation in columns J to AG.

I have highlighted issues i currently have i.e. in cells J13 to O13 where there should be no values in J13 to N13 as the date placed in service is 24th of March 2024 and the Mar'23 depreciation in cell O13 should only be for 7 days rather than a full month.

On other thing to note is the way the asset life and remaining life in columns AJ and AK is quoted e.g. 4.8 = 4 years and 8 month not 4.8 years.

Any help would be greatly appreciated.

 

[hagia_sofia]

It would be really helpful if you could post here the formulas that you would like to adjust.

 

[exce101]

@hagia_sofia Thanks for your response and apologies my intention was to include my current formulas ... hit the submit button too quickly.

Formula I currently have for existing assets is as follows:
Cell J14 has the formula: IFERROR(IF(IF($A14>J$2,$AM14,($A14-A$2)*$I14)<0,0,IF($A14>J$2,$AM14,($A14-A$2)*$I14)),0)

For new assets in the current year, the formula i have attempted is as follows:
Cell J13 (formula not currently in the cell in the previous post screenshot): =IF($D13="Yes", IF($AH13>J$2,0,(J$2-$AH13)*$I13))

If i applied the above formula in row 13, it would look as follows however as you can see, March depreciation calculation is correct but April onwards is incorrect.

Need to find a way to combine the two formulas and fix the second one. Not sure if i am on the right track with these so far.

 

[exce101]

i'm adding the calculation rules below which may provide additional context:

1. If it is a new asset in the current financial year and the date place in service is greater than the current month, then depreciation = 0

2. If it is a new asset in the current financial year and the date place in service is prior to the current month, then calculate the depreciation for the start month of when the asset is placed in service based on # of days and subsequent monthly depreciation will be the monthly amount (column AM) until the end of life month where the depreciation will be tapered to the exact # of days.

3. If it is an existing asset and the asset doesn't reach full life in the current year, then current month depreciation = monthly depreciation amount (column AM)

4. If it is an existing asset and the asset reaches full life in the current year, then the monthly depreciation for the month in which the asset is at end of life, calculate the depreciation for the month based on # of days

 

[Tetra201]

... formula to calculate monthly depreciation (exact to the number of days)...

How do you plan to handle the following situation: your asset on row 13 has a life of 56 months. It is placed in service on 24-Mar-2024, and its end of life is 24-Nov-2028. So, it has 55 full months of depreciation @1,349.83/mo, 7 days of depreciation in March (i.e., 7/31 of the month), and 24 days of depreciation in November (i.e., 24/30 of the month). However, 7/31+24/30 ≠ 1.

 

[exce101]

@Tetra201 good challenge - i should have posted yesterday that I managed to make some progress on the formula and adding it below for scrutiny. I have amended the end of life for row 13 to 28/11/2024 to test the formula and it doesn't capture the asset ending in FY25.

I need to tweak it further to take this into account this - any ideas welcome. Perhaps creating a new check column to reference the formula to.

Formula in cell: J14

=IF(IF(AND($D14="Yes",$C14="Yes"),
IF(J$2-$AH14>27,$AM14,
IF(AND(J$2-$AH14>0,J$2-$AH14<28),(J$2-$AH14)*$I14,0)),
IF($D14<>"Yes",IFERROR(IF(IF($A14>J$2,$AM14,($A14-I$2)*$I14)<0,0,IF($A14>J$2,$AM14,($A14-I$2)*$I14)),0)))=FALSE,0,
IF(AND($D14="Yes",$C14="Yes"),
IF(J$2-$AH14>27,$AM14,
IF(AND(J$2-$AH14>0,J$2-$AH14<28),(J$2-$AH14)*$I14,0)),
IF($D14<>"Yes",IFERROR(IF(IF($A14>J$2,$AM14,($A14-I$2)*$I14)<0,0,IF($A14>J$2,$AM14,($A14-I$2)*$I14)),0))))

 

[Tetra201]

Try pasting the following formula in cell J13 and drag-copying it to the right as needed:

=LET(n,MAX(0,MIN(EOMONTH(J$2,0),$A13)-MAX(EOMONTH(J$2,-1),$AH13)),IF(n=DAY(EOMONTH(J$2,0)),$AM13,$I13*n))

 

[exce101]

@Tetra201 thank you so much for getting back to me. This works at initial glance ... will play around with dates and different scenarios.

Never used a LET formula before however i've just tried to work it out and follow through your formula and it is so much better than the long winded one i was attempting.

Thanks once again - i learnt something new today

 

[Tetra201]

You are most welcome.