Excel formula to enhance depreciation pro-rated formula-CodePudding

I am building a file to calculate the depreciation amount of several items with various capitalization dates and depreciation rates. I currently have worked something out but it seems too basic, I am trying to fit as many of the already created formulas from column I to N into one to make it nice and clean. I am struggling to mix & match formuals to get there. Any suggestions on which formuals I should use is most welcomed.

From column A to G, it is data generated by a system. From Column I to N is the couple of basic formulas I have added.

Cell I13 has: =YEAR($C$10)-YEAR(B13) Cell J13 has: =MONTH($C$10)-MONTH(B13) 1 Cell K13 has: =(I13*12) J13 Cell L13 has: =VLOOKUP(A13,$B$4:$D$8,3,FALSE) Cell M13 has: =VLOOKUP(A13,$B$4:$E$8,4,FALSE) Cell N13 has: =IF(K13<M13,-C13*K13/M13,-C13)

The depreciation is calculated from the month of purchase of the asset (capitalisation date) and not by day.

CodePudding user response：

I to N on one formula

=-MIN($C13,$C13/VLOOKUP($A13,$B$4:$E$8,4,0)*(DATEDIF($B13,$C$10,"M") 1))

CodePudding user response：

You could make your life easier using Excel 'hidden' DATEDIF() function.
=DATEDIF(startDate, endDate, "M") will return the difference in months.
There is a good explanation about it here and from Microsoft here. You will have to type it manually, strangely it is not available in the list of functions.