Inputting a length of time?

  • Thread starter Thread starter Link
  • Start date Start date
L

Link

Hi

I am trying to calculate depreciation in my Excel spreadsheet. The
depreciation is over 60 months, so for a $6,000 item, the formula is
=$6,000/60 = $100

The problem I am encountering is making that calculation stop after 60
months. So in month 61, the cell reads $0, instead of $100. Is there anyway
in Excel to 'time limit' the effectiveness of a cell's formula?

To make it even more complicated, I will have ongoing items for
depreciation, so I will need to add new items to the formula, while
simultaneously letting existing items expire, as above.

It's pretty hard to explain, so I imagine it will be pretty hard to solve,
but I would certainly appreciate any advice.

Cheers,
Link.
 
Let's say your initial value is in A1 and the length of time is in A2. I'm
also assuming that after the length listed, the value is equal to zero.
Let's also assume that the # of months since the initial value is A3.

=if(A3>=A2,(A1*A3)/A2,0)

I think that's what you want.

HTH,
Barb Reinhardt
 
Hi

Assuming you set out your schedule as follows
A Asset Name
B Purchase Date
C Asset Value
D Number of Months to Depreciate
E End of starting month e.g 31 Jan 2006
Format cell E, Format Cells>Number>Custom> mmm yy so it displays as Jan
06
Drag cell E1 across the screen, holding down the fill handle, on
releasing the mouse button, choose Fill Months

Now in cell E2 enter
=IF($A2="","",IF(AND(E$1>=$B2,E$1<=DATE(YEAR($B2),MONTH($B2)+$D2,DAY($B2))),$C2/$D2,0))
and copy across, and down

If you have the Analysis Toolpak loaded, Tools>Addins>check Analysis
Toolpak, then you could use the shorter formula
=IF($A2="","",IF(AND(E$1>=$B2,E$1<=EOMONTH($B2,$D2-1)),$C2/$D2,0))
 
Hi Roger

I tried what you said. Firstly I couldn't find the option to choose Fill
Months upon releasing my mouse button after dragging. Then I got these
results:

Asset Name Purchase Date Asset Value Number of Months to depreciate
End of Starting Month
StudyStation Jun-07 $10,000 60 Jan-00


Changing the number of months to depreciate, has no affect on the date. I'm
just not sure what is supposed to be going on there.

I need to find the ending date of the depreciation, but I also need a column
to show the monthly amount and it should return to zero after the
depreciation time.

Would you be kind enough to expand further, please. I really appreciate it.

Cheers,
Link.
 
Hi

I should have said hold down the right mouse button as you drag, then on
release of button choose Fill months.

Secondly, it looks as though you have cell E2 formatted as Date, it
should be Number, with as many places as your require.
What you should then see, assuming you have 31/12/07 in E1, is
0,0,0,0,0,166.7,166.7,166.7 etc.
in cells E2:L2

If you are still having difficulty, let me have your email address and I
will send you a sheet already set up.
 
assuming you have 31/12/07 in E1

That should have read
assuming you have 31/01/07 in E1

(my dates are UK format)

Also, I didn't comment upon your statement
Changing the number of months to depreciate, has no affect on the date
Click to expand...

It doesn't, and is not supposed to. It just works out when to end
depreciation, and the amount of depreciation to charge per month.
 
I think I am finally getting somewhere now Rog.

Thanks for your perseverance. I just have two more questions (please):

It seems to go for one month too many. In the following example, the
payments go for five months instead of four, and include one extra payment:

Asset Name Purchase Date Asset Value Number of Months to depreciate
Jun-07 Jul-07 Aug-07 Sep-07 Oct-07
StudyStation Jun-07 $10,000 4 2500 2500 2500 2500 2500


What happens if I have another asset, say in month three? How can I include
that asset in the depreciation payment, and still have them both exist for
their respective depreciation periods?

Thanks again and I look forward to your reply.

Cheers,
Link.
 
In regards to my last question, I was hoping to have all items depreciating
in one row, as opposed to adding up multiple rows.

Cheers,
Link.
 
Hi

I was assuming the date you would be entering for asset purchase, would
either be beginning of month 01/06/07 or mid month
15/06/07. If you use those dates, then it does depreciate over the
correct number of months.

As far as additional assets are concerned, they would be entered on
succeeding rows.
The total depreciation for each month would be the sum of all of the
assets individual depreciation amounts in that column.
 
So is there no way to incorporate all assets into one row? Thing is I have
so many rows in this bad boy that it's getting a little out of control. Is
there any way to add the formulas or add the previous cell. I know it's a
tall order, but it'd really help out.

Thanks Roger,
Link.
 
Hi

I can't see any way of incorporating into a single row.
For each asset you need the Purchase date and the number of months over
which to depreciate.
There is no way that could be achieved in a single row with multiple
assets.

What is the problem with having multiple rows?
This would be typical of most depreciation schedules, which typically
would reside on a separate sheet.
The total for each month from this schedule, would then be picked up as
a single row of values in any financial projection.
 
Thanks Roger - I will do the proper thing and make the schedule!;-)

Thanks very much for all your help - it is much appreciated.

Cheers,
Link.
 
Back
Top