<center>
![An XLSref Investigation: Testing Excel's DB()](https://images.ecency.com/DQmPtw4JJVhzPECR75dZXYiz4HxKyxWcEzuw5obPFviSkXd/an_xlsref_investigation_testing_excel_2003_s_db_.png)
</center>

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

***

<center>
<sup><i>Images are edited screen captures from either Excel 2003 or LeoThreads.  Edits made using MS Paint.</i></sup>
</center>

***

## 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](https://leofinance.io/threads/topics/XLSref) entry at [LeoThreads](https://leofinance.io/threads) for function [`DB()`](https://leofinance.io/threads/@magnacarta/re-leothreads-7gggq4), I encountered a series of formulas used to calculate [depreciation](https://leofinance.io/@leoglossary/leoglossary-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:
<center>
[![Excel 2003 Help page for DB](https://images.ecency.com/DQmcz1a6pQQMnunAWorV16eYgVcyP2SmagkbxJqCHRv8QKp/db_00.png)<br /><sup>Click to view full-size image</sup><br /><hr />](https://images.ecency.com/DQmcz1a6pQQMnunAWorV16eYgVcyP2SmagkbxJqCHRv8QKp/db_00.png)
</center>
Here is how those formulas were translated for XLSref:
<center>
[![Thread 6 of 9](https://images.ecency.com/DQmT4Sf9y3iCPv67QAezFgiZRDqxkkkh7U4PTjPrPJCdWqX/db_6of9.png)<br /><sup>Click to view Thread</sup><br /><hr />](https://leofinance.io/threads/@magnacarta/re-leothreads-3ffcde)
[![Thread 8 of 9](https://images.ecency.com/DQmcqFjuLkb6zz9xtiyQr3WboSeEfk6NDaF49evDFy4ZpKs/db_8of9.png)<br /><sup>Click to view Thread</sup></sup><br /><hr />](https://leofinance.io/threads/@magnacarta/re-leothreads-2uf2zu)
[![Thread 9 of 9](https://images.ecency.com/DQmeuE7ibXs8HvyQw61p9ywnJur8H7Q3RLmYHgP2FAmSSS4/db_9of9.png)<br /><sup>Click to view Thread</sup><br /><hr />](https://leofinance.io/threads/@magnacarta/re-leothreads-6akzdg)
</center>

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

<center>
[![Behind-the-scenes formulas which make DB() function](https://images.ecency.com/DQmcxSrZCPsUFqb2iFF5Ju7BHh6PmjqZyv4mi7XQDmHdW1j/db_01.png)
<br /><sup>Click to view full-size image</sup><br /><hr />](https://images.ecency.com/DQmcxSrZCPsUFqb2iFF5Ju7BHh6PmjqZyv4mi7XQDmHdW1j/db_01.png)
</center>

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:
<center>
[![Table for calculated Rate, Not Rounded](https://images.ecency.com/DQmXt3ZajH7Yj5dHaKqfHHrYFx6qocVbDHf49251ZZxY24S/db_02.png)<br /><sup>Click to view full-size image</sup><br /><hr />](https://images.ecency.com/DQmXt3ZajH7Yj5dHaKqfHHrYFx6qocVbDHf49251ZZxY24S/db_02.png)
</center>

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 Y<sub>_i_</sub> | MonthYi=$A$20 | 12 | $81,999.64 | 82047.19 | ($47.55) | 100.0580% |
| 5 | Months in Y<sub>_f_</sub> | 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**

<sup>(Emphasis added)</sup>

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)**

<sup>(Emphasis added)</sup>

Rounded to Precision 3, the rate to be used is 0.319.  For this reason, the [named range](https://leofinance.io/threads/@magnacarta/re-leothreads-iywat) Rate was modified from `Rate=$A$22` to `Rate=$A$32`.  All other [named ranges](https://leofinance.io/threads/@magnacarta/re-leothreads-66zhtq) kept their assigned references.

## Second Trial

Using the figures from the updated table, these are the results of my second trial:
<center>
[![Table for calculated Rate (Precision 3)](https://images.ecency.com/DQmbpEzbFzH6Au4opgQCGMx8mgQAb4ZZ3AN3ndZVHmUhqYf/db_03.png)<br /><sup>Click to view full-size image</sup><br /><hr />](https://images.ecency.com/DQmbpEzbFzH6Au4opgQCGMx8mgQAb4ZZ3AN3ndZVHmUhqYf/db_03.png)
</center>
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 Y<sub>_i_</sub> | MonthYi=$A$20 | 12 | $81,999.64 | 81999.64 | $0.00 | 100.0000% |
| 5 | Months in Y<sub>_f_</sub> | 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.
<center>
![Thank You for Reading.  Keyboard Warriors Wanted.](https://images.ecency.com/DQmU9FU26QYLU8WNWJuPJNqyXVSrwmQ1jjJSy21jDR3Up5v/typing_cat_kbd.gif)
</center>

<center>
![aboutmc_1.png](https://images.ecency.com/DQmUWPHC3oGV1h3VsN6Gx8pcALhVMkq1WXWRm9ENgXEYSPe/aboutmc_1.png)
</center>

<center>
![aboutmc_2a.png](https://images.ecency.com/DQmUMujEGdnLZWG2Bx8mHXrJCAY7rCgXXZVwsFQTwqGHRS8/aboutmc_2a.png)
</center>

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

<center>
![aboutmc_3.png](https://images.ecency.com/DQmbCXQEaZE5VWdnxYmHJFfrbKXtCQkQtAbWTvzxXzAEviC/aboutmc_3.png)
</center>

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


Posted Using [LeoFinance <sup>Beta</sup>](https://leofinance.io/@magnacarta/an-xlsref-investigation-testing-excel-s-db)