Solved

Excel need help figuring out a forumla

Posted on 2014-01-23
4
187 Views
Last Modified: 2014-01-23
I am having problems trying to figure out a formula here and hope someone can help me out.

Yr      Value      Depreciation %      Depreciation Amount      New Value
1      100                       20%                                   20                   80
2      80                       40%                                   32                   48
3      48                       60%                                  28.8                  19.2
4      19.2                      80%                                          15.36                  3.84
5      3.84                     100%                                  3.84                    0

As you can see year 1 it depreciates a full 20% and the new value of year 1 becomes 80, but what if I am trying to get a value that is 18 month, which would include a Year 1 and half of year two.  What should be the formula if I just want to plug in 18 as a value?  TIA
0
Comment
Question by:WooYing
  • 2
4 Comments
 
LVL 8

Expert Comment

by:itjockey
ID: 39805530
Hi WooYing,

See attached file.




Thanks
Depreciation.xlsx
0
 
LVL 3

Expert Comment

by:Sreeram
ID: 39805537
HI
   
     Form your question what i got is  you need to show year as 1.6,3,4.6,6...so on.if it is correct you can use below formula.


Try this :

Cell   A1  =>   1.6
Cell   A2  =>   3
Cell   A3  =>    =A1+3
Cell   A4 =>   =A2+3

For the Following cell just drag the selection in the column 'A'
0
 
LVL 8

Accepted Solution

by:
itjockey earned 300 total points
ID: 39805544
You have to add one column for how many months you want to calculate depreciation. which I had in attached Column D. see attached new version in which you have only add values in Column D (months to depreciate).

Thanks
Depreciation.xlsx
0
 
LVL 81

Assisted Solution

by:byundt
byundt earned 200 total points
ID: 39805562
Wouldn't you figure 1.5 years of depreciation as 1 year at 20% and a half year at 40%? I realize that the depreciation is probably governed by accounting and tax rules in your country, but I am trying to apply logic where logic may not necessarily be invited.

If my assumption is correct, then assuming a starting value of $100, you would have $80 after 1 year and $64 after 1.5 years.

The array-entered formula for this would be:
=B1*PRODUCT(100%-OFFSET(G3,0,0,B2+1))*(100%-(B2-INT(B2))*VLOOKUP(B2+1,tbDepreciation,2))

To array-enter a formula:
1.  Select the cell, then click in the formula bar
2.  Hold the Control and Shift keys down
3.  Hit Enter, then release all three keys
Excel should respond by adding curly braces { } surrounding the formula. If not, repeat steps 1 to 3.

In the above formula, B1 is the initial value and B2 is the number of years of depreciation. G3 is the top left cell in a two column table called tbDepreciation that contains the number of years of depreciation in the first column and the depreciation rate in the second column. This table starts with year 0 and 0% depreciation.

Note: my formula assumes that you enter the number of years as a number with a decimal fraction. For 18 months, you would enter 1.5. For 36 months, you would enter 3. If you would rather enter months directly, then replace B2 with B2/12 wherever you see it in my formula.
DepreciationTableQ28347065.xlsx
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

939 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now