formualizer_eval/builtins/financial/
depreciation.rs

1//! Depreciation functions: SLN, SYD, DB, DDB
2
3use crate::args::ArgSchema;
4use crate::function::Function;
5use crate::traits::{ArgumentHandle, CalcValue, FunctionContext};
6use formualizer_common::{ExcelError, LiteralValue};
7use formualizer_macros::func_caps;
8
9fn coerce_num(arg: &ArgumentHandle) -> Result<f64, ExcelError> {
10    let v = arg.value()?.into_literal();
11    match v {
12        LiteralValue::Number(f) => Ok(f),
13        LiteralValue::Int(i) => Ok(i as f64),
14        LiteralValue::Boolean(b) => Ok(if b { 1.0 } else { 0.0 }),
15        LiteralValue::Empty => Ok(0.0),
16        LiteralValue::Error(e) => Err(e),
17        _ => Err(ExcelError::new_value()),
18    }
19}
20
21/// SLN(cost, salvage, life)
22/// Returns the straight-line depreciation of an asset for one period
23#[derive(Debug)]
24pub struct SlnFn;
25impl Function for SlnFn {
26    func_caps!(PURE);
27    fn name(&self) -> &'static str {
28        "SLN"
29    }
30    fn min_args(&self) -> usize {
31        3
32    }
33    fn arg_schema(&self) -> &'static [ArgSchema] {
34        use std::sync::LazyLock;
35        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
36            vec![
37                ArgSchema::number_lenient_scalar(),
38                ArgSchema::number_lenient_scalar(),
39                ArgSchema::number_lenient_scalar(),
40            ]
41        });
42        &SCHEMA[..]
43    }
44    fn eval<'a, 'b, 'c>(
45        &self,
46        args: &'c [ArgumentHandle<'a, 'b>],
47        _ctx: &dyn FunctionContext<'b>,
48    ) -> Result<CalcValue<'b>, ExcelError> {
49        let cost = coerce_num(&args[0])?;
50        let salvage = coerce_num(&args[1])?;
51        let life = coerce_num(&args[2])?;
52
53        if life == 0.0 {
54            return Ok(CalcValue::Scalar(
55                LiteralValue::Error(ExcelError::new_div()),
56            ));
57        }
58
59        let depreciation = (cost - salvage) / life;
60        Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)))
61    }
62}
63
64/// SYD(cost, salvage, life, per)
65/// Returns the sum-of-years' digits depreciation of an asset for a specified period
66#[derive(Debug)]
67pub struct SydFn;
68impl Function for SydFn {
69    func_caps!(PURE);
70    fn name(&self) -> &'static str {
71        "SYD"
72    }
73    fn min_args(&self) -> usize {
74        4
75    }
76    fn arg_schema(&self) -> &'static [ArgSchema] {
77        use std::sync::LazyLock;
78        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
79            vec![
80                ArgSchema::number_lenient_scalar(),
81                ArgSchema::number_lenient_scalar(),
82                ArgSchema::number_lenient_scalar(),
83                ArgSchema::number_lenient_scalar(),
84            ]
85        });
86        &SCHEMA[..]
87    }
88    fn eval<'a, 'b, 'c>(
89        &self,
90        args: &'c [ArgumentHandle<'a, 'b>],
91        _ctx: &dyn FunctionContext<'b>,
92    ) -> Result<CalcValue<'b>, ExcelError> {
93        let cost = coerce_num(&args[0])?;
94        let salvage = coerce_num(&args[1])?;
95        let life = coerce_num(&args[2])?;
96        let per = coerce_num(&args[3])?;
97
98        if life <= 0.0 || per <= 0.0 || per > life {
99            return Ok(CalcValue::Scalar(
100                LiteralValue::Error(ExcelError::new_num()),
101            ));
102        }
103
104        // Sum of years = life * (life + 1) / 2
105        let sum_of_years = life * (life + 1.0) / 2.0;
106
107        // SYD = (cost - salvage) * (life - per + 1) / sum_of_years
108        let depreciation = (cost - salvage) * (life - per + 1.0) / sum_of_years;
109
110        Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)))
111    }
112}
113
114/// DB(cost, salvage, life, period, [month])
115/// Returns the depreciation of an asset for a specified period using the fixed-declining balance method
116#[derive(Debug)]
117pub struct DbFn;
118impl Function for DbFn {
119    func_caps!(PURE);
120    fn name(&self) -> &'static str {
121        "DB"
122    }
123    fn min_args(&self) -> usize {
124        4
125    }
126    fn variadic(&self) -> bool {
127        true
128    }
129    fn arg_schema(&self) -> &'static [ArgSchema] {
130        use std::sync::LazyLock;
131        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
132            vec![
133                ArgSchema::number_lenient_scalar(),
134                ArgSchema::number_lenient_scalar(),
135                ArgSchema::number_lenient_scalar(),
136                ArgSchema::number_lenient_scalar(),
137                ArgSchema::number_lenient_scalar(),
138            ]
139        });
140        &SCHEMA[..]
141    }
142    fn eval<'a, 'b, 'c>(
143        &self,
144        args: &'c [ArgumentHandle<'a, 'b>],
145        _ctx: &dyn FunctionContext<'b>,
146    ) -> Result<CalcValue<'b>, ExcelError> {
147        let cost = coerce_num(&args[0])?;
148        let salvage = coerce_num(&args[1])?;
149        let life = coerce_num(&args[2])?;
150        let period = coerce_num(&args[3])?;
151        let month = if args.len() > 4 {
152            coerce_num(&args[4])?
153        } else {
154            12.0
155        };
156
157        if life <= 0.0 || period <= 0.0 || !(1.0..=12.0).contains(&month) {
158            return Ok(CalcValue::Scalar(
159                LiteralValue::Error(ExcelError::new_num()),
160            ));
161        }
162
163        let life_int = life.trunc() as i32;
164        let period_int = period.trunc() as i32;
165
166        if period_int < 1 || period_int > life_int + 1 {
167            return Ok(CalcValue::Scalar(
168                LiteralValue::Error(ExcelError::new_num()),
169            ));
170        }
171
172        // Calculate rate (rounded to 3 decimal places)
173        let rate = if cost <= 0.0 || salvage <= 0.0 {
174            1.0
175        } else {
176            let r = 1.0 - (salvage / cost).powf(1.0 / life);
177            (r * 1000.0).round() / 1000.0
178        };
179
180        let mut total_depreciation = 0.0;
181        let value = cost;
182
183        for p in 1..=period_int {
184            let depreciation = if p == 1 {
185                // First period: prorated
186                value * rate * month / 12.0
187            } else if p == life_int + 1 {
188                // Last period (if partial year): remaining value minus salvage
189                (value - total_depreciation - salvage)
190                    .max(0.0)
191                    .min(value - total_depreciation)
192            } else {
193                (value - total_depreciation) * rate
194            };
195
196            if p == period_int {
197                return Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)));
198            }
199
200            total_depreciation += depreciation;
201        }
202
203        Ok(CalcValue::Scalar(LiteralValue::Number(0.0)))
204    }
205}
206
207/// DDB(cost, salvage, life, period, [factor])
208/// Returns the depreciation of an asset for a specified period using the double-declining balance method
209///
210/// TODO: KNOWN ISSUES - This implementation has correctness issues that need to be fixed:
211/// 1. Fractional period handling is incorrect - Excel does NOT support fractional periods
212///    for DDB and returns an error. The current implementation attempts a weighted average
213///    approach which doesn't match Excel behavior.
214/// 2. Salvage value logic may not match Excel exactly - Excel's DDB doesn't consider salvage
215///    during the per-period depreciation calculation; it only prevents cumulative depreciation
216///    from exceeding (cost - salvage).
217///
218/// See merge-review/03-financial.md for full analysis.
219#[derive(Debug)]
220pub struct DdbFn;
221impl Function for DdbFn {
222    func_caps!(PURE);
223    fn name(&self) -> &'static str {
224        "DDB"
225    }
226    fn min_args(&self) -> usize {
227        4
228    }
229    fn variadic(&self) -> bool {
230        true
231    }
232    fn arg_schema(&self) -> &'static [ArgSchema] {
233        use std::sync::LazyLock;
234        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
235            vec![
236                ArgSchema::number_lenient_scalar(),
237                ArgSchema::number_lenient_scalar(),
238                ArgSchema::number_lenient_scalar(),
239                ArgSchema::number_lenient_scalar(),
240                ArgSchema::number_lenient_scalar(),
241            ]
242        });
243        &SCHEMA[..]
244    }
245    fn eval<'a, 'b, 'c>(
246        &self,
247        args: &'c [ArgumentHandle<'a, 'b>],
248        _ctx: &dyn FunctionContext<'b>,
249    ) -> Result<CalcValue<'b>, ExcelError> {
250        let cost = coerce_num(&args[0])?;
251        let salvage = coerce_num(&args[1])?;
252        let life = coerce_num(&args[2])?;
253        let period = coerce_num(&args[3])?;
254        let factor = if args.len() > 4 {
255            coerce_num(&args[4])?
256        } else {
257            2.0
258        };
259
260        if cost < 0.0 || salvage < 0.0 || life <= 0.0 || period <= 0.0 || factor <= 0.0 {
261            return Ok(CalcValue::Scalar(
262                LiteralValue::Error(ExcelError::new_num()),
263            ));
264        }
265
266        if period > life {
267            return Ok(CalcValue::Scalar(
268                LiteralValue::Error(ExcelError::new_num()),
269            ));
270        }
271
272        let rate = factor / life;
273        let mut value = cost;
274        let mut depreciation = 0.0;
275
276        for p in 1..=(period.trunc() as i32) {
277            depreciation = value * rate;
278            // Don't depreciate below salvage value
279            if value - depreciation < salvage {
280                depreciation = (value - salvage).max(0.0);
281            }
282            value -= depreciation;
283        }
284
285        // TODO: Handle fractional period - this logic is incorrect and doesn't match Excel
286        // Excel returns an error for non-integer periods. This weighted average approach
287        // should be removed or replaced with proper error handling.
288        let frac = period.fract();
289        if frac > 0.0 {
290            let next_depreciation = value * rate;
291            let next_depreciation = if value - next_depreciation < salvage {
292                (value - salvage).max(0.0)
293            } else {
294                next_depreciation
295            };
296            depreciation = depreciation * (1.0 - frac) + next_depreciation * frac;
297        }
298
299        Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)))
300    }
301}
302
303pub fn register_builtins() {
304    use std::sync::Arc;
305    crate::function_registry::register_function(Arc::new(SlnFn));
306    crate::function_registry::register_function(Arc::new(SydFn));
307    crate::function_registry::register_function(Arc::new(DbFn));
308    crate::function_registry::register_function(Arc::new(DdbFn));
309}
formualizer_eval/builtins/financial/depreciation.rs

formualizer_eval/builtins/financial/
depreciation.rs
