Hi all,
I’m trying to keep track of my rental property deductions and depreciation schedules and am stuck in Excel trying to get some maths done.
I’m trying to apply the Diminishing Value Method but am not an Excel guru like some of you!
The math is easy enough to do by hand for each item but I can’t get it to work out in Excel.
I’m aware that a QS would do this schedule and that’s what we have for the 1st year but any investments/repairs/etc after that is not reflected and I’d like to be able to track it ourselves using all our receipts so that I have a clean table to provide our accountant each FY.
The formula I’m referring to is outlined below and in this page under the section “Calculating deductions for decline in value”: https://www.ato.gov.au/individuals-and-families/investments-and-assets/property-and-land/residential-rental-properties/rental-expenses/depreciating-assets-in-rental-properties
Asset's cost × (days held ÷ 365) × (200% ÷ asset's effective life)
Notes:
- “Days held” is referring to the number of days held in the financial year when first purchased or installed so can be replaced by (Financial year end date - Purchase or Install date) on the 1st year but then by (Financial year end - Financial year start)
- Given that the asset’s effective life is defined by an official table, I will simplify things and it can a cell with a value in years for each item (e.g. 5)
- As each financial year goes on the asset cost is recalculated to reflect the initial asset cost minus all previous depreciation years.
The result should be something like the below although I’m not stuck to the form.
Asset name | Asset price | Asset purchase date | Asset effective life | 1st FY depreciation | 2nd FY depreciation | ….
E.g.
Washing machine | $1400 | Jan 25 2020 | 5 | $240.21 | $463.92 | $278.35 | etc…
NB about example above:
- Purchase date falls in the 2019-2020 financial year, which is a leap year (366 days), but the formula requires to always divide by 365
- Asset was held 157 days in the 1st financial year (Jun 30 2020 - Jan 25 2020)
- The adjusted asset cost on the 2nd financial year is $1159.79 (1400 - 240.21) and so on..
Thanks in advance for all suggestions!
PS: if this is the wrong place to ask for help please do let me know.
A.
6,454 views
1 replies