Xcelsius supports a number of financially oriented functions relating to depreciations, inter- est type calculations, return on investment, and the like.
DB,DDB,SLN,SYD, and VDB DB(cost,salvage,life,period,month) DDB(cost,salvage,life,period,factor) SLN(cost,salvage,life) SYD(cost,salvage,life,period) VDB(cost,salvage,life,start_period,end_period,factor,no_switch)
93 Mathematical and Statistical Functions in Xcelsius 2008
4 Depreciation is a way to allocate the use of an asset over its lifetime. There are different
methods of depreciation, which take into account things like the cost of an asset, its lifespan, and a salvage value at the end of its useful life.
The best way to gain familiarity with the wide diversity of depreciation methods and their calculations is to use them interactively so that you can examine their behavior “in the wild” and, more importantly, learn how to construct a spreadsheet and dashboard that puts their features to use. Open the file ch04_Depreciation.xlf(see Figure 4.9) and try out combina-
tions of parameters. Figure 4.9 A depreciation exploratorium for comparing a variety of methods, options, and values.
This dashboard allows you to choose two depreciation methods from any of a number of dif- ferent possible methods; the four that are used here are declining balance, sum of the year’s digits, double declining balance, and straight-line depreciation. To accomplish this, you use context switching. To spice things up a bit, you can add an additional construction—the ability to display depreciation expense, year by year, or to show the cumulative depreciation. Here is how it works. You don’t bother creating a calculation for year-by-year expense and then another one for cumulative expenses. Instead, you can use a multiplication factor (cell B4), which is either going to be a 0or a 1, depending on whether the data to be charted is
yearly or cumulative. If it is yearly, B4 is set to 0, and if it is cumulative, B4 is set to 1.
N O T E
Chapter 5 discusses the graphical features and enhancements of spreadsheet construction.
94 Chapter 4 Embedded Spreadsheets: The Secret Sauce of Xcelsius 2008
4
You just add the current year’s depreciation expense, B4, multiplied by whatever was reported in the prior year. So if the depreciation toggle is set to 1, the tally is automatically
cumulative. The following is the simplified version of the formula for cell C6:
=E6+$B$4*C5
where the current year’s depreciation expense relevant to the data series in column C is in column E.
To make the calculation results a little easier to handle, you do some rounding. To make this formula a bit more robust, you can turn off the formula by sensing when the current year is beyond the maximum lifespan of the asset. The final formula is then as follows:
=IF($B6=””,””,ROUND(E6+$B$4*C5,0))
All sorts of visual refinements can be made to this dashboard, as you’ll learn in later chapters.
FV,IPMT,IRR,MIRR,NPER,NPV,PMT,PPMT,PV, and RATE
FV(rate,nper,pmt,pv,type) IPMT(rate,per,nper,pv,fv,type) IRR(values,guess) MIRR(values,finance_rate,reinvest_rate) NPER(rate,pmt,pv,fv,type) NPV(rate,value1,value2,...) PMT(rate,nper,pv,fv,type) PPMT(rate,per,nper,pv,fv,type) PV(rate,nper,pmt,fv,type) RATE(nper,pmt,pv,fv,type,guess)
In this section, we’ll focus on the NPVfunction, which deals with net present value.
Like physical assets that depreciate over time, the value of money can diminish over time. Given the choice of having a hundred thousand dollars today or waiting 10 years to receive a hundred thousand dollars, which option would you choose? Most people would correctly choose to have the money now. There are several good reasons for this. In 10 years from now, you may not be around to enjoy the money. Also, inflation is likely to kick in during the intervening time, so a hundred thousand dollars will buy you less wealth then than it can today. Most importantly, you can invest that money today and generate greater wealth over the next 10 years.
Any way you look at it, there’s a time value of money that tends to deflate at a certain rate if you do nothing with it. In effect, net present value analysis answers the question, “What is my investment paid out today really worth if I get an income stream down the road?” The “really worth” is not only dependent on future cash flows but also the rate at which money changes value over time.
You can easily set up net present value calculations on a spreadsheet in Xcelsius 2008. The
NPVfunction basically has two arguments: a rate and a range of cells to tabulate the cash
flow. Figure 4.10 shows how this might be set up.
The essential idea is that cash flows are represented on a timeline. In Figure 4.10, the cash flow can be found on row 7. The rate at which the value of money dissipates over time is set in the input cell A4 and can be controlled using a horizontal slider.
95 Mathematical and Statistical Functions in Xcelsius 2008
4
Figure 4.10 Net present value analysis lends itself to interactive visualization.
In addition, the initial investment or cost (input cell A3) is set by using a second slider on the canvas. Notice that the initial cost or investment (cell A3) is automatically converted into a negative number in the cash flow.
Net present value is computed on row 8, using the following formula in cell C8:
=NPV(Rate,$B$7:C7)
Notice that B7 is an absolute or fixed location, but C7 is a relative reference. As the formula is replicated to the right, the number of cells evaluated for the net present value increases. On the combination chart shown in Figure 4.10, the cash flow is displayed as a column chart, and the net present value is rendered in the form of a line chart.
N O T E
It is easy to visually find the internal rate of return. You simply adjust the rate on the dashboard slider so that the NPV at the end of the projected timeline is 0. When the net present value reaches 0at the end of the timeline, the rate shown on the slider will match the internal rate of return.