An XLSref Investigation: Testing Excel's DB()

Did Excel 2003's DB() function have a bug? Who knows? That's why I investigated this for XLSRef (powered by LeoThreads).

---

^{Images are edited screen captures from either Excel 2003 or LeoThreads. Edits made using MS Paint.}

---

Background

In workplace settings, I used whatever the current version of MS Office (and, therefore, Excel) was, and that changed several times. For personal use, I was using various versions of an open source suite of MS Office-compatible applications named LibreOffice, and Calc was its spreadsheet.

Since I needed to explain things using a more familiar menu structure, I had purchased an ancient version of Excel. Price point determined how ancient I would go, and for a while I was using Excel 2000 before I "upgraded" to Excel 2003.

Recently at XLSRef (powered by LeoThreads)

While preparing the XLSref entry at LeoThreads for function DB(), I encountered a series of formulas used to calculate depreciation using the fixed-declining balance method along with the formula for calculating the rate of depreciation.

These financial formulas are straightforward, but because of how they were presented in the Excel 2003 Help page for DB() I wasn't sure if I was typing them properly into XLSref. This is how the equations appeared in Help:

^{Click to view full-size image}

---

Here is how those formulas were translated for XLSref:


^{Click to view Thread}

---

^{Click to view Thread}

---

^{Click to view Thread}

---

The equations looked OK, but how would they translate into actual spreadsheet use? My greatest concerns were with exponentiation and rounding errors.

^{Click to view full-size image}

---

So I tested DB() against formulas based on the XLSref entry I made for DB() at LeoThreads.

First Trial

Using the same figures from the Excel 2003 Help page, I set up a small table as shown below:

^{Click to view full-size image}

---

Here is the same table in Markdown:

Value	Factor	Named Range	Months	DB()	Formulas	DB()-Formulas	% of DB()
1000000	Cost	Cost=$A$16	7	$186,083.33	185912.96	$170.37	99.9084%
100000	Salvage	Salvage=$A$17	12	$259,639.42	259456.00	$183.42	99.9294%
6	Period (years)	Life=$A$18	12	$176,814.44	176765.31	$49.13	99.9722%
,,,	,,,	,,,	12	$120,410.64	120428.81	($18.17)	100.0151%
7	Months in Yi	MonthYi=$A$20	12	$81,999.64	82047.19	($47.55)	100.0580%
5	Months in Yf	MonthYf=$A$21	12	$55,841.76	55898.10	($56.34)	100.1009%
0.318707931	Rate, Not Rounded	N/A	5	$15,845.10	15867.89	($22.79)	100.1438%

Things To Note:

1️⃣Period is for 6 years, but DB() was used 7 times

The initial year is a partial year, covering just 7 months. This year requires the "Initial DB" formula.

The final year is also a partial year, covering just 5 months (12-7). This year requires the "Final DB" formula

Together they make up 1 year of depreciation. The middle 5 years are each full years, so that's 6 total years.

2️⃣Discrepancies between DB() and "DB()-Formulas" are due to Rate

In some years the discrepancy is positive, and in some negative. The discrapancies range from 99.9084% to 100.1438%, for a spread of 0.2354%. For some situations, that may be OK. For a business needing to account for depreciation-- not to mention to keep the taxing authorities at bay-- it is unacceptable.

3️⃣XLSref formulas work-- with one exception

Although the Help page for DB() mentioned this detail, it didn't register with me until some time later:

rate = 1 - ((salvage / cost ) ^ (1/life)), rounded to three decimal places

^{(Emphasis added)}

Using the Rate formula just quoted, it worked as expected and returned 0.318708.

Accounting for the rounding requirement, this is the revised Rate formula used in the new table:

=ROUND( 1-((Salvage/Cost)^(1/Life)) ,3)

^{(Emphasis added)}

Rounded to Precision 3, the rate to be used is 0.319. For this reason, the named range Rate was modified from Rate=$A$22 to Rate=$A$32. All other named ranges kept their assigned references.

Second Trial

Using the figures from the updated table, these are the results of my second trial:

^{Click to view full-size image}

---

Here is the same table in Markdown:

Value	Factor	Named Range	Months	DB()	Formulas	DB()-Formulas	% of DB()
1000000	Cost	Cost=$A$16	7	$186,083.33	186083.33	$0.00	100.0000%
100000	Salvage	Salvage=$A$17	12	$259,639.42	259639.42	$0.00	100.0000%
6	Period (years)	Life=$A$18	12	$176,814.44	176814.44	$0.00	100.0000%
,,,	,,,	,,,	12	$120,410.64	120410.64	$0.00	100.0000%
7	Months in Yi	MonthYi=$A$20	12	$81,999.64	81999.64	$0.00	100.0000%
5	Months in Yf	MonthYf=$A$21	12	$55,841.76	55841.76	$0.00	100.0000%
0.318707931	Rate, Not Rounded	N/A	5	$15,845.10	15845.10	$0.00	100.0000%

Once Rate was set to Precision 3, the formulas matched DB() and the discrepancies disappeared.

Just My Two Sats

It can be tricky translating equations and formulas into a microblogging format. This is why I wanted to confirm that the equations I included in the XLSref entry for DB() worked as intended.

Initially I found discrepancies, then I discovered one detail I hadn't incorporated: Rate is rounded to 3 decomal places.

After adding that requirement, my spreadsheet formulas matched the DB() as expected.

A large portion of the hundreds of spreadsheet functions are for finance and statistics. Given that these are based on formulas and equations too complex for a microblogging context, I will publish recurring posts featuring my tests and validation of those formulas and equations.

Links in image	URLs
Hive Business Center	/created/hive-123507
Hivelist Classifieds	/created/hive-150840
Hive Hustlers	https://www.hivehustlers.com/
#LeoThreads	https://leofinance.io/threads
Hive Projects	https://hiveprojects.io
Dust Sweeper	@dustsweeper
Dust Bunny	@dustbunny

Links in image	URLs
"...to make up for..."	https://leofinance.io/hive-167922/@magnacarta/who-is-magnacarta-and-why-is-he-sending-me-pizza
LeoFinance	https://leofinance.io/@magnacarta
Proof of Brain	https://proofofbrain.blog/@magnacarta
♦ D.Buzz-only ♦ D.Buzz: ♦ at-magnacarta.buzz	https://blog.d.buzz/#/@magnacarta.buzz
Festivus	https://festivusweb.com/index.php