G
Guest
Dear Experts
I am trying to create a depreciation table with the details mentioned below.
I hope what I am trying to convey below is clear enough to understand.
I have columns for Purchase Date, Cost, Rate, Year Days (Closing
Date-Purchase Date, which should not be more than 365 or 366 days), Total
Days (Closing Date-Purchase Date=Actual number of days), Opening Depreciation
and Year's Depreciation.
The method is Straight Line method and the rate is 20% per annum. So in 5
years an asset is fully depreciated.
The Year's Depreciation is based on actual days from the date of purchase.
So I request your help to write a formula based on the following situations.
Case 1
If Opening Depreciation is equal to Cost, then the result in the Column
"Year's Depreciation" should be Zero.
Case 2
If Opening Depreciation (OD) is less than the Cost, the result should be 20%
of Cost provided that the total of Opening Depreciation + Year's Depreciation
is not greater than Cost.
If OD=$95, then in the Year's Depreciation Column, I should get the result
of $5
Case 3
If an asset is purchased for $100 during the year say on 1-Jul-07 and
Closing Date is 31-Dec-07. The total days is 184, then Year's Depreciation
will be calculated as follows
=100x20%x184/365=$10.08 which should be the result in the Year's Depreciation.
Case 4
If the Purchase date entered is greater than closing date, (meaning 1-Jan-08
and closing date is 31-Dec-07) the result in Year's Depreciation should be
Zero
I have tried using the IF / Min and SLN function but the desired results do
not match the cases I want.
Thank you in advance for your help.
BR
I am trying to create a depreciation table with the details mentioned below.
I hope what I am trying to convey below is clear enough to understand.
I have columns for Purchase Date, Cost, Rate, Year Days (Closing
Date-Purchase Date, which should not be more than 365 or 366 days), Total
Days (Closing Date-Purchase Date=Actual number of days), Opening Depreciation
and Year's Depreciation.
The method is Straight Line method and the rate is 20% per annum. So in 5
years an asset is fully depreciated.
The Year's Depreciation is based on actual days from the date of purchase.
So I request your help to write a formula based on the following situations.
Case 1
If Opening Depreciation is equal to Cost, then the result in the Column
"Year's Depreciation" should be Zero.
Case 2
If Opening Depreciation (OD) is less than the Cost, the result should be 20%
of Cost provided that the total of Opening Depreciation + Year's Depreciation
is not greater than Cost.
If OD=$95, then in the Year's Depreciation Column, I should get the result
of $5
Case 3
If an asset is purchased for $100 during the year say on 1-Jul-07 and
Closing Date is 31-Dec-07. The total days is 184, then Year's Depreciation
will be calculated as follows
=100x20%x184/365=$10.08 which should be the result in the Year's Depreciation.
Case 4
If the Purchase date entered is greater than closing date, (meaning 1-Jan-08
and closing date is 31-Dec-07) the result in Year's Depreciation should be
Zero
I have tried using the IF / Min and SLN function but the desired results do
not match the cases I want.
Thank you in advance for your help.
BR
