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

Only conjecture on my part, but the reason you may not have had any replies
is that this smacks somewhat of being a homework/coursework question. Now
whilst that doesn't in itself preclude any help here, it is expected that an
attempt is made to answer the questions on your part, and then you let us
know what you have tried, quoting any formulas you have used, and what part
you may still need help with.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------



"AJ" wrote in message
...
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