EXCEL Real time problems and solution (Covering real time financial, accounting and day to day office problems) Shivprasad Koirala Sham Sheikh Visit us at http://www.questpond.com for more details. This book is a sample book. For buying a hard copy please contact shiv_koirala@yahoo.com or call at 9967590707. We conduct EXCLUSIVE excel classes in Mumbai for working class people. After the course you can solve your day to day cryptic office issues with ease. Please call 9967590707 for more details. Common Formulas....................................................................................................................... 3 How to find average of a given set of results?......................................................................... 3 What is the use of Goal seek command? ................................................................................. 3 How do we concatenate in excel?............................................................................................ 4 Investment, loan and Interest calculation..................................................................................... 5 How can we calculate compounding interest in excel? ........................................................... 5 How do we calculate future and present value for series of cash flow? .................................. 7 What are data tables and how can it helps us?......................................................................... 9 To achieve 61051 INR after 5 yrs at 10% interest rate what should be the yearly investment amount?.................................................................................................................................. 18 How do we calculate number of years for given rate of interest and loan value? ................. 19 How do we calculate rate of interest? .................................................................................... 20 You have two models of investments you want to know which is the best?......................... 21 How can we compare a business plan investment with fixed rate returns?........................... 22 How do we calculate a value of a bond using excel? ............................................................ 23 How can we calculate Current yield, YTM and YTC for bonds? ......................................... 26 You have two stocks how will you decide the investment strategy for the same?................ 28 You have three stocks you want to choose two stocks with diversified investment?............ 30 Deprecation ................................................................................................................................ 31 How do we calculate straight line depreciation? ................................................................... 31 What is double declining depreciation?................................................................................. 32 How do we calculate depreciation using ‘sum of year digit’?............................................... 34 How can we calculate depreciation for a certain time period? .............................................. 35 Common Formulas How to find average of a given set of results? Many times you get data as shown in figure ‘Average in action’. In the below scenario we have number of items sold for every month. We need to find average of sales across all these months. Figure ‘Average in action’ is also numbered. So let’s try to understand the same according to numbers. 1 and 2Æ This represents the months and corresponding sales to those months. 3 Æ We have used the formulae ‘Average’ and selected the items sold every month. In the right hand side of the same figure we can see the final average value. We have sold approximately 274 items on an average every month. Figure: - Average in Action What is the use of Goal seek command? In normal scenarios excel takes input and gives out some output. Sometimes situation demands that depending on output we need to find what the input is. Let’s try to understand what does this mean. Consider the below situation outlined in figure ‘problem’ below. In the table we have sales percentage for every year . At the end of the row we have calculated the average of the percentage profit. This average is calculated for four years. Now we need to find out to achieve a profit percentage of 35 how much sales should do at the fifth year. So now we need to calculate input by which we can achieve an output (35 % average). Figure: - Problem We can calculate the same by using ‘goal seek’ command. So click on toolsÆ goal seek and you should be popped with a goal seek dialog box shown below. There are three values first is the ‘set cell’. ‘set cell’ column is nothing but the output cell so in this case it’s the cell which displays average value (B7). Second column is the ‘to value’. This column specifies what is the output targeted. As said previously we are targeting average of 35% so we specify the value 35. The third column ‘By Changing Cell’ is the input which needs to be calculated for the targeted output. In this case it’s the fifth year column. Once we specify all the three columns we are all set to get the input calculated for the target output. Click ok and you should see the auto adjusted value for input (fifth year sales percentage) and output (average) as shown in figure ‘Goal Seek in Action’. Figure: - Goal seek in action How do we concatenate in excel? Concatenation is one of the common problems which occur in excel documentation. For instance below figure ‘Concatenation in action’ shows we need to concatenate the telephone number with code. While doing the concatenation we also need to separate the telephone number and area code with hyphen (“-“). This can be easily achieved by using the ‘Concatenate’ keyword. ‘Concatenate’ keyword takes series of string value and displays the same in a concatenated fashion. You can see from the figure the first is the area code, second input is the hyphen “-“and the final is the telephone number. We have also shown the actual output for the same in the same figure. Figure: - Concatenation in action Investment, loan and Interest calculation How can we calculate compounding interest in excel? Before we get in to how to calculate compound interest in excel. We need to understand three important concepts present value (PV), future value (FV) and compounding. Let’s say you have 2000 INR and you give it to a bank for fixed deposit with rate of interest of 10%. So this 2000 INR is your present value. Now after one year with 10 % interest you will get 2200. So 2200 is your future value. Now lets say you again put 2200 for the next year with 10% rate of interest you will get 2420, this is termed as compounding. Below figure ‘Compounding in action’ shows how the compounding fundamentals work. In the same figure we have also give the formulae which is used to calculate compounding interest. Figure: - Compounding in action Excel provides ‘FV’ formula to calculate compounding interest. Using formula ‘FV’ the compounding interest calculation becomes a cool breeze. Below figure ‘FV in action’ shows how it can be used. It has five parameters. ‘PMT’ and ‘Type’ is zero we will get in to details of both these parameters in the coming questions. ‘Rate’ is the rate of interest, ‘nper’ is the number of years for which interest has to be calculated and ‘pv’ is the present value. So with ‘present value’ equal to 2000, interest rate of 10% for two years gives us 2420 by applying compounding formula. Figure: - FV in action Now let’s give a simple twist to the above example, what if we want to calculate present value depending on future value and rate of interest. So let’s consider the same example. Below figure ‘PV in action’ shows how we can do the reverse calculation if we are given the future value and interest rate. Figure: - PV in action Note: - Type parameter tells at which preiod the transaction occurs. If it happens at the start of period ( i.e. at 1st Jan of the year) then type = 1 and if it occurs at the end of the year ( i.e. 31st December) then type =0. How do we calculate future and present value for series of cash flow? In the previous question we had only a single cash flow. In other terms we had a present value (2000) which was then carried further year by year with a specific rate of interest (10 %). Now let’s take a scenario where you need to pay cash every month with a specific rate of interest. So you have series of cash flows and you need to calculate the present value and future value. So let’s consider the below scenario of retirement planning. During the retirement stage we expect to withdraw every month 10,000 INR. We have identified a bank which pays 10% interest so how much should you invest so that you can achieve your monthly target. Figure: - Cash flow In this case we need to calculate the present value we need to invest so that we can get 10,000 INR every month. In the previous question we have already used PV formula. We need to use the same but with a bit twist as the scenario is of a series of cash flow and not a single cash flow. Below figure ‘PV for series of cash flow’ shows how we should use PV for series of cash flow. For series of cash flow we need to specify the ‘pmt’ value and give ‘fv’ value as zero. In this case we have assumed that the transaction will occur at the end of period so we have give type=0. So rate is nothing but the interest rate , ‘nper’ is the number of years, pmt is the expected withdrawal per month , fv is zero as it’s a single cash flow and type =0. In the same figure we have also given the output. So by investing today 37907 INR we should be able to withdraw 10,000 INR every year for the coming five years. Figure: - PV for series of cash flow Now let’s understand the same other way around. You are investing 10,000 every month, with 10% rate of interest, so how much should you get?. Below figure ‘Future value’ shows the same in a pictorial format. If you look at the problem its nothing we need to find the future value for series of cash flow. Figure: - Future value To find future value we need to use the ‘FV’ formula. Below is a full snapshot which shows how we can use the formula. We have specified rate of interest, period and ‘pmt’ value. As this is a series of cash flow we have specified ‘pv’ and ‘type’ as zero. So if we invest for 5 years with 10,000 INR every year with 10% rate of interest we will get 61051 INR. Figure: - Future value in action Note :- One of the important points to be noted is we need to specify ‘pmt’ value when we are dealing with series of cash flow. We do nto specify ‘pmt’ when we have single cash flow. What are data tables and how can it helps us? Consider the scenario below as shown in figure ‘Two input view’ where we want to have an overall picture of what will be the return on various values of interest and period. << Figure: - Two input view >> The above complex view can be achieved by using ‘Datatables’ in excel. A Data Table will give a view of how by changing certain values in your formulas you can affect the result of your formula. Data Tables can store the results of many different scenarios for you in one table, so that you can analyze them to select which scenario is optimum. ‘Datatables’ can take one input or two inputs. The above figure has two inputs ‘Interest’ and ‘Period (Number of years)’. So let understand both the ways of analyzing data i.e. ‘Single input’ way and ‘Two input’ way. Let’s take a small sample which calculates future value as shown in figure ‘Single Input Scenario’. So with an initial amount of 2000 invested for 5 years with 8% interest we will get 11733.20 after 5 years. We have found the same by using ‘FV’ formula. If you want to understand ‘FV’ in more detail please refer previous questions. << Figure: - Single input scenario >> Now we need to find out that with different rate of interest what will be the ‘FV’ i.e. Future value. In the same figure ‘Single input scenario’ we have entered rows from 8% to 20%. In the first row i.e. for 8% we have referenced the column where we have the ‘FV’ formula. This is a very important step if you miss it you will not get proper results. For this scenario the input is the ‘rate of interest’ and we need to find out how much will be the future value after 5 years. In short there is only one input. << Figure: - Select Area for single input >> So the first thing we do is select the area as shown in figure ‘Select area for single input’ and click on data Æ table. Yu will be then popped with dialog box as shown in figure ‘Dialog box for single input’. The dialog box has two parameters one is the row input cell and the other is the column input cell. Currently we have only one input i.e. ‘Rate of interest’. We do not have a second input currently. So we need to only enter the cell reference of ‘Rate of interest’ in the column input cell of the table dialog box. Once done click and VOILA. You can see the output shown in the same figure for the rate of interest for 5 years period. << Figure: - Dialog box for single input >> Now we add a bit of twist, we add one more input. In other words we add one more input period (number of years). So for various range of interest and period what is the future value. So as said previously we have two inputs one is the rate of interest and the other is period. In the below figure ‘Two input situation’ we have entered years row wise (1, 2, 3,4,5,6 and 7) and percentage column wise (4% to 17%). Again we have referred the ‘FV’ formula on the row where period is present. This is again a very important step. The position where the reference is done is also important. << Figure :- Two input situation >> Now we select the area we want to the result and click data Æ table. << Figure :- Select area for two inputs >> We are then popped up with a table dialog box as shown in figure ‘Dialog box popup for two inputs’. We have two inputs one is the column input i.e. rate of interest and second is the row input i.e. number of years. Now click ok and VOILA. You can see how EXCEL has calculated the future value for every combination of interest and period. We have also shown the output in the same figure ‘Dialog box popup for two inputs’. << Figure :- Dialog box popup for two inputs >> To achieve 61051 INR after 5 yrs at 10% interest rate what should be the yearly investment amount? This is typical problem where we need to find monthly investment for particular return after a specific period. So let’s take the above case where we need to get 61051 INR after 5 yrs. We know a bank which gives yearly 10% rate of interest. So how much should we invest on a a yearly basis to get 61051 INR. To get the yearly investment amount we can use the PMT function as shown in the figure ‘PMT in action’ below. We have taken the target amount as FV i.e. future value. So to get 61051 INR we need to invest 10,000 INR on a yearly basis. Figure: - PMT in action How do we calculate number of years for given rate of interest and loan value? Now let’s reverse the scenario mentioned in the previous question. So we know the target amount, rate of interest and future value and we need to find how much period it will take to reach that future value. This can be achieved by using the ‘nper’ function. Below figure ‘nper’ in action shows what we need to feed in and the output for the same. Figure: - Nper in action Note: - One of the points you can see in the ‘nper’ function is the negative ‘pmt’. In excel when money flows out we mark it as negative and when money flows in we mark it as positive. How do we calculate rate of interest? Let’s say we have scenario where we know how much we have for investment, how much we expect and how many years we can keep the investment for?. Depending on these parameters we need to find out at what rate our investment will grow. Below figure ‘rate in action’ shows the same in a pictorial format. We need to use ‘rate’ formula. In the below scenario we are investing 37907 INR (this is nothing but the PV) for 5 years and we are expecting a return of 610 51 INR (this is the FV). One of the points to be noted is the negative PV value. As said previously any fund flowing outside should be marked as negative. So we conclude that the growth rate is of 10%. Figure: - Rate in action If you see the above figure there is one more parameter i.e. guess. Guess allows you to give some type of guess for a rate. This is not a required argument. If you omit it takes a value of 10. Note: - Till now all the above calculation of rate, PV and FV are done on the assumption that we have an even cash flow. We can not use the above formula in scenarios where we have uneven cash flow. The coming question will target more on how to handle unequal cash flow calculations. You have two models of investments you want to know which is the best? First lets understand what the question means. Let’s say we are having two business models and we need to invest money in to the same. Below figure ‘Investment models’ shows the same in a pictorial format. The first investment model is to open a grocery shop and the second investment model is to buy a car. All the investment in to both the businesses are shown by negative (-) sign i.e. it signifies that cash has flown out. While positive (+) sign signifies that we have received money from the business in other words it’s the profit. So for the grocery business:- • We need to buy grocery items (240000) • Maintain the shop (5000) • We need to pay for the salary for a grocery person (50000). The total profit after the sale of grocery items is 400000. The second investment model is of a car business. • We need to buy a car (240000) • Pay maintenance for the car (6000) • Pay salary for the driver (24000). The rent received or the profit in the car business is 360000. Note :- One of the important point to be noted is we can not use PV,FV and rate formula as they are used only when cash flows are even. In this case the cash flows are uneven so this will need a different approach. The current example is very simple and has hardly three or four transactions. You can easily guess which business is good by simple maths. In real world scenario entries can go in thousand and it will be difficult to predict which business model is feasible. The best way to predict any business growth is by the rate of return it gives. So if we are able to calculate the rate of return from both these business we can easily decide which investment we should go for. Rate of return can be calculated by using the IRR formula. Figure: - Investment models Below figure ‘IRR in action’ shows how we can use the IRR formulae. We just need to select the full range and input the same in the IRR formula. << Figure: - IRR in action >> So finally calculating the rate of return we find that the grocery investment gives more return than the car investment. Figure: - Rate of return How can we compare a business plan investment with fixed rate returns? Let’s first try to understand what this question means. For instance consider the below investment shown in figure ‘NPV in action’. We have made an investment plan of 10,000 and then in the first year we predict to get 2000, in second year 4000 and so on. The total profit we predict to get is 34000 at the end of the investment plan. Before we move ahead to implement this business investment we would like to analyze , what if we put the same in fixed deposit at 5% rate will it be better than this business plan. The answer is to use the NPV function. Below figure shows how we have used ‘NPV’. One of the important points to be noted is we have not taken the investment amounts. By using NPV we get 28227. That means to get the same return we would need to invest 28227 in fixed deposit. The same we are getting by only investing 10000, which means this business plan, is better than 5% fixed deposit plan. Figure: - NPV in action Note: - Some times NPV and IRR can be very misleading. They are estimates and not actuals. So please do recheck yourself the results before implementing the same. How do we calculate a value of a bond using excel? Organization need fund for expansion now and then. There are two ways they can raise funds one is they can issue shares or they can borrow. When an organization borrows money it’s termed as bonds. Bonds are debt. So we can say the bond issuer (organization) is the borrower and the bond investor is the lender. Figure: - Bonds Concept The bond issuer pays periodic interest payments to the bond investor and repays the principal at the maturity. The interest payment is also termed as coupon payment. The coupon payment can be yearly, half yearly or quarter yearly. So the investor pays a principal, he gets regular coupons or interest payments and finally when the maturity finishes he gets the principal back. Below figure ‘bond payments’ shows the same in a more pictorial format. Figure: - Bond payments So lets take scenario and try to understand how we can do bond calculation using excel. Let’s consider the below scenario for bond calculation. Settlement date 2/24/2010 Maturity date 4/23/2030 Coupon rate 8% Target rate of return 9% Redemption Value 100 Frequency 1 Basis 0 What should be the value of the bond? Table: - Bond example The bond is for 20 years (from 2/24/2010 to 4/23/2030) and the issuer promises to pay 8% on a yearly basis. We are expecting 9% percent returns so what should be the value of the bond?. Excel provides a ‘price’ function which can make our life easy for solving bond calculations. ‘Price’ function is not available as a normal formula. We need to install the ‘Analysis toolpak’ for the same. To install analysis toolpak click on tools Æ Add-IN’s. You will be popped up with a screen as shown below. Check the ‘Analysis toolpak’ and click ok and we are ready to use the ‘price’ function. Figure: - Add-Ins installation Click on ‘Fx’ Æ select ‘Financial’ function and you should be able to use the ‘price’ function. Figure: - Price function Now let’s map the above problem with the formula. You can see how the problem can be easily mapped with ‘price’ formula. So let’s understand each attribute step by step. Settlement: - This date is on which money and security exchange hands. Maturity: - This is date on which the last coupon payment is done and also the principal amount is returned. Rate: - This is the coupon rate. In the current scenario it’s annually because the frequency is 1 i.e. annually. Yld: - This is required rate of return you are looking. Redemption: - This is the amount per 100 of the face value when the bond is redeemed. For instance if the bond value is 720 INR then that is the 100 of the face value. If the bond issuer says that we will give a redemption of 106 that means you will get 763 INR as the redemption value. Frequency: - This is the number of coupons paid each year. So its 1, 2 or 4 (annual, semiannual or quarter). Basis: - This describes the method of counting the number of days between two dates. There are several methods, each of which makes different assumptions about how to count. 30/360 assumes that each month has 30 days and that there are 360 days in a year. Actual/360 counts the actual number of days, but it assumes that there are 360 days in a year. Actual/Actual counts the actual number of days in each month, and the actual number of days in a year. In Excel bond functions, 0 signifies 30/360, 1 specifies actual/actual, 2 is actual/360, 3 is actual/365 (which ignores leap days), and 4 represents the European 30/360 methodology. Figure: - Price in action The ‘price’ function always gives the percentage value. To find the actual value we need to multiply the percentage with bond price when it was listed. For the above calculation we will get 91%. So if the price of the bond is 7000 we need to buy the same at 6370 to get 9% return rate. The percentage calculation helps us to the compare the same with return of other bonds. Note: - One of the most important aspects in any investment is what returns does it give back, same holds true for bonds. There are many methods to calculate the return from bonds YTM and YTC are the most used , we will try to cover the same in the coming questions. How can we calculate Current yield, YTM and YTC for bonds? Before buying any bond any one would like to compare two bonds and see which one will give high rate of return. There are three ways of calculating rate of return / yield on bonds current yield, yield to maturity and yield to call. Current yield: - This methodology gives is a rough estimate because it excludes compounding interest and change in bond prices due to market. Below figure ‘Current Yield in action’ shows a simple sample of coupon rate of 8%, redemption of 100 and value of bond as 91% of the principal. So the current yield is 8.7%. Please note we have take percentage values of bond rather than actual values as it helps us compare two bonds of different actual face values. Note :- There is no built in formulae for current yield in EXCEL. Figure: - Current Yield in action YTM: - The current yield methodology has a major drawback that it does not take in to account market prices, interest gains and capital gains. YTM also termed as ‘Yield to maturity’ is compound annual rate of return expected if the bond is held till maturity. In the below figure ‘Yield in action’ we have take a simple bond sample and used the ‘yield’ function. The only extra parameter in this is the bond price. We have taken the percentage of the bond price you can take the actual, but then you need to multiply the same with percentage factor. So 8.9 % is the answer which takes in to account the interest gain and capital gain in this bond example. << Figure: - Yield in action >> Yield to call: - There are scenarios where the organization will like to buy back the bonds if interest rate decreases or probably the organization wants to call of the bond. Consider the below example where the maturity date is ‘4/23/2030’ but the organization called off the bond on ‘2/2/2012’. In case the bond is called off the organization is ready to pay 104% call price of the face value. We used the same yield formula only that maturity date is the call of date and redemption is the call price. So for this example in case the organization calls of the bond we will get 15.6% returns. Figure: - Yield to call You have two stocks how will you decide the investment strategy for the same? There are two important things which need to be considered while making decision between stocks. First is how much return the stocks will give and second is the amount of risk associated with the stock. So when we want to compare any two stocks we should first find these two values. To compare two stocks we also need to have history for the same. Below figure ‘Stdev in action’ shows two stocks (stock A and stock B) with rate of return every year. The first thing we need to find is which stock gives us good return on an average. So using the ‘average’ formula we have found out that Stock A gives us good return than Stock B. As said previously rate of return should not be the only criteria for evaluating that we need to buy the stock or not we also need to find out how much percentage risk is associated with the stock. If the stock is volatile it has higher risk. So if we are able to find out how much the stock varies we can measure the volatility of the same. The best way to find volatility is by measuring how much is the standard deviation in the stock. Standard deviation is a measure of how widely and far are the values spread. So if the values are spread too far that means there is high level of volatility. We have used ‘stdev’ formula to calculate standard deviation. ‘stdev’ takes range of values and calculates the deviation according to the spread. From the results we can see that deviation of Stock A is more than Stock B. So Stock A has high level of risk than Stock B. Now which you choose completely depends on personal judgment. My call on this is Stock A. Its worth to leave the 0.86% of profit than taking 5% extra risk. Figure: - STDEV in action Now let’s see the magic of diversification. Let’s invest 50% in stock A and 50% in stock B. So you can see from the figure ‘Diversified Investment’ we have multiplied both the stock value by 0.5 i.e 50% investment. By doing this we are getting 11.43% returns with only taking 2.6% risk. That’s the power of diversified investment. We have almost brought down our risk 1/4th time. Figure: - Diversified investment You have three stocks you want to choose two stocks with diversified investment? First let’s try to understand this question. In the below example ‘Stock diversification sample’ we have three stocks ‘Stock A’,’ Stock B’ and ‘Stock C’. You have decided to buy ‘Stock A’. Now you want to buy either ‘Stock B’ or ‘Stock C’ in such way that it should give you diversified portfolio. In short you want to find out which stock from ‘B’ and ‘C’ has no relation or least relation with ‘Stock A’. This can be achieved by using the ‘CORREL’ function. ‘CORREL’ takes two collection of values (in this case it is the stock values) and gives you a number between 1 to -1. Value What does it mean? More the value near to ‘1’ This means both the value are related. So more near to ‘1’ more is the relationship. More the value near to ‘-1’ This means both the values are not related. So more near to -1 more independent are the value. Table: - CORREL values So using the ‘CORREL’ function we get that stock A and stock B have a value of ‘-0.98’ and for stock A and C we have value of ‘0.89’. So stock A and stock B are more diversified than Stock A and Stock C. Figure: - Stock diversification sample Deprecation How do we calculate straight line depreciation? First let’s try to understand what is depreciation all about ?. Any asset we buy for an organization has a finite life time. As time passes by asset looses value. It helps us to evaluate the wear and tear for fixed assets over a period of time. Straight line depreciation is the simplest and widely used technique. Below figure ‘straight line depreciation’ shows the complete formula for the same. Fixed cost asset is the total cost of the asset. Scrap value is the asset value when it will be finally sold or disposed. For instance let’s say you have bought a computer and you use it for 5 years. Even after five years the computer will still sell for some amount. There is a possibility depending on asset that the scrap value can be zero. Life span of the asset is the total life time of the asset. Figure: - Straight line depreciation To calculate straight line depreciation we need to use the ‘SLN’ formulae. It takes three values the fixed cost which is termed as cost in excel , scrap value also called as salvage value and the life of the asset. This returns us depreciation calculation per month. Figure: - SLN in action Let’s try to understand the same with a simple formula. In the below figure ‘SLN formulae in action’ we cal see we have defined a fixed cost, scrap value and life in years. On the right hand side you can see how a ‘SLN’ formula takes those inputs. In the same figure we have also shown the output for the same. If you try calculating using ‘Straight line depreciation’ formulae discussed previously you should arrive to the same answer. Figure: - SLN formulae in action First thing to note is that the depreciation is calculated monthly. Now if we spread the depreciation for five year (which is the life span of the asset) you will get a balance of 2000. This balance is nothing but the scrap value of the asset. Below table ‘Final depreciation calculation’ shows how the values are spread for five years, the total depreciation and the balance left. Years First year Second Year Third Year Fourth Year Fifth Year Depreciation Value 2600 2600 2600 2600 2600 Total Depreciation 13000 Balance 2000 Table: - Final depreciation calculation What is double declining depreciation? Double declining is type of acceleration depreciation method. Accelerated depreciation means it recognizes a higher depreciation at the beginning of the life time of the asset. First let’s understand what declining appreciation is ?. Double declining depreciation value is twice the value of the straight line depreciation. In double declining previous years asset value becomes the input to the next year’s depreciation calculation. Below figure ‘Double declining balance’ shows how calculations are done for every year. The basic formula for calculating depreciation is as below. Depreciation expense = previous period value * ( Factor / Life of Asset ) From the above formula we will get depreciation expense on a per month basis. Previous period value is the value of the asset after deducting the depreciation of that year. Factor value is two for double declining depreciation. Life of asset is the life of the asset. So let’s analyze the below calculation for double declining balance. In the below example we have asset of cost around 19000 with scrap value of 2000 and life of the asset is five years. So the first year of calculation is fixed cost multiplied by factor divided by life of asset which comes to around 7600. Now to calculate for the second year we subtract the depreciation of the first year from the fixed cost and multiple by factor divided by life of asset. In the same way we carry forward for third and fourth year. Now for the fifth year we deduct the previous brought forward from the scrap value, because this is the final year of the life of asset. So for the final year the depreciation is 462. Try to follow the way carry forwards are taken from previous year to the next year this will clear your fundamentals in a more appropriate manner. Figure: - Double declining balance Ok, the above calculation was very tedious. Excel has provided an easy way i.e. by using ‘DDB’ formula. Below figure ‘DDB in action’ shows how the formula does the calculation. It takes five parameters. We have out arrows to the parameter to understand the same in more visual format. Figure: - DDB in action How do we calculate depreciation using ‘sum of year digit’? Sum of year digits is a form of accelerated depreciation. Using this methodology we have greater depreciation in the earlier years and less in the later years. Below is a simple sample which shows the cost of asset, life and the salvage value. For the same we will try to calculate depreciation using ‘sum of year digit’. Cost of Asset 10000 Rs Life of Asset 5 Years Salvage Value 2000 Rs Table: - Sample example So below are the steps to calculate depreciation using ‘sum of year digit’. • The first thing we do is total the number of year using life of the asset. As shown above the total number of years is five so the total is 1 + 2 + 3 + 4 + 5 = 15. • To calculate depreciation for the any year we subtract the salvage value from the cost of asset and multiply it by a factor which varies depending on which year it is. Sum of year digit depreciation = (Cost of Asset – Salvage value) * Factor To calculate factor below is the formula. Factor = (Year for which depreciation is calculated) / (Total of the number of years) Currently the total of the number of years is 15 (1+2+3+4+5). So below is the calculation of factor for five years. 1st year factor is (5/15=0.33), 2nd year factor is (4/15=0.26), 3rd year factor is (3/15=0.2) and so on. In order to calculate depreciation we need to multiply this factor to the difference between cost of asset and salvage value. So below table shows a detail calculation for the same. Year (Cost of asset – Salvage value) * factor Depreciation for the year Table: - Depreciation calculation using sum of year Figure: - SYD in action How can we calculate depreciation for a certain time period? Sometimes you need to find depreciation for a certain period in the life time of the asset. For instance your life time of the asset is five year but you only want to calculate for the first three years. This is where VDB comes to rescue. Below figure shows the various parameters. You can see the ‘from period’ and the ‘to period’ parameter. Factor is the rate by which the decline happens. If the factor is omitted then it’s taken as 2 which means it’s a double declining balance method. If you do not want double decline then put a rate if decline factor in this parameter. We also have a ‘no_switch’ parameter is ‘FALSE’ then the formula uses straight line depreciation and if it’s ‘TRUE’ then it does not use straight line depreciation. First (10000-2000) * (5/15) 2666.67 Second (10000-2000) * (4/15) 2133.33 Third (10000-2000) * (3/15) 1600.00 Fourth (10000-2000) * (2/15) 1066.67 Fifth (10000-2000) * (1/15) 533.33 Figure: - VDB in action Note :- To understand straight line depreciation refer the previous questions in this chapter. For instance below is the sample of the VDB formula. The total cost of the asset is 19000, with scrap value of 2000, life time of five years and the depreciation is calculate for the first two periods. By default the ‘no_switch’ is false so its using double declining methodology to calculate depreciation. Figure: - Sample VDB formula For instance the below figure shows how we have calculated depreciation for the first two years. In the right hand side we have calculated depreciation for two years using VDB. In the left hand side we have calculated depreciation using double decline for five years. If you add up the first two years depreciation expense it’s the same which we have got from the VDB formula. Figure: - VDB in action