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 |