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/// Returns straight-line depreciation for a single period.
22///
23/// `SLN` spreads the depreciable amount (`cost - salvage`) evenly across `life` periods.
24///
25/// # Remarks
26/// - Formula: `(cost - salvage) / life`.
27/// - `life` must be non-zero; `life = 0` returns `#DIV/0!`.
28/// - This function returns the algebraic result: if `salvage > cost`, depreciation is negative.
29/// - Inputs are interpreted as scalar numeric values in matching currency/period units.
30///
31/// # Examples
32///
33/// ```yaml,sandbox
34/// title: "Straight-line yearly depreciation"
35/// formula: "=SLN(10000, 1000, 9)"
36/// expected: 1000
37/// ```
38///
39/// ```yaml,sandbox
40/// title: "Negative depreciation when salvage exceeds cost"
41/// formula: "=SLN(1000, 1200, 2)"
42/// expected: -100
43/// ```
44/// ```yaml,docs
45/// related:
46///   - SYD
47///   - DB
48///   - DDB
49/// faq:
50///   - q: "Can `SLN` return a negative value?"
51///     a: "Yes. If `salvage > cost`, `(cost - salvage) / life` is negative."
52///   - q: "What happens when `life` is zero?"
53///     a: "`SLN` returns `#DIV/0!`."
54/// ```
55#[derive(Debug)]
56pub struct SlnFn;
57/// [formualizer-docgen:schema:start]
58/// Name: SLN
59/// Type: SlnFn
60/// Min args: 3
61/// Max args: 3
62/// Variadic: false
63/// Signature: SLN(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar)
64/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
65/// Caps: PURE
66/// [formualizer-docgen:schema:end]
67impl Function for SlnFn {
68    func_caps!(PURE);
69    fn name(&self) -> &'static str {
70        "SLN"
71    }
72    fn min_args(&self) -> usize {
73        3
74    }
75    fn arg_schema(&self) -> &'static [ArgSchema] {
76        use std::sync::LazyLock;
77        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
78            vec![
79                ArgSchema::number_lenient_scalar(),
80                ArgSchema::number_lenient_scalar(),
81                ArgSchema::number_lenient_scalar(),
82            ]
83        });
84        &SCHEMA[..]
85    }
86    fn eval<'a, 'b, 'c>(
87        &self,
88        args: &'c [ArgumentHandle<'a, 'b>],
89        _ctx: &dyn FunctionContext<'b>,
90    ) -> Result<CalcValue<'b>, ExcelError> {
91        let cost = coerce_num(&args[0])?;
92        let salvage = coerce_num(&args[1])?;
93        let life = coerce_num(&args[2])?;
94
95        if life == 0.0 {
96            return Ok(CalcValue::Scalar(
97                LiteralValue::Error(ExcelError::new_div()),
98            ));
99        }
100
101        let depreciation = (cost - salvage) / life;
102        Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)))
103    }
104}
105
106/// Returns sum-of-years'-digits depreciation for a requested period.
107///
108/// `SYD` applies accelerated depreciation by weighting earlier periods more heavily.
109///
110/// # Remarks
111/// - Formula: `(cost - salvage) * (life - per + 1) / (life * (life + 1) / 2)`.
112/// - `life` and `per` must satisfy: `life > 0`, `per > 0`, and `per <= life`; otherwise returns `#NUM!`.
113/// - The function uses the provided numeric values directly (no integer-only enforcement).
114/// - Result sign follows `(cost - salvage)`: positive for typical depreciation expense, negative if `salvage > cost`.
115///
116/// # Examples
117///
118/// ```yaml,sandbox
119/// title: "First SYD period"
120/// formula: "=SYD(10000, 1000, 5, 1)"
121/// expected: 3000
122/// ```
123///
124/// ```yaml,sandbox
125/// title: "Final SYD period"
126/// formula: "=SYD(10000, 1000, 5, 5)"
127/// expected: 600
128/// ```
129/// ```yaml,docs
130/// related:
131///   - SLN
132///   - DB
133///   - DDB
134/// faq:
135///   - q: "Does `SYD` require integer `life` and `per`?"
136///     a: "No strict integer check is enforced; it uses provided numeric values directly after domain validation."
137///   - q: "Which period values are valid?"
138///     a: "`per` must satisfy `0 < per <= life`, and `life` must be positive; otherwise `#NUM!` is returned."
139/// ```
140#[derive(Debug)]
141pub struct SydFn;
142/// [formualizer-docgen:schema:start]
143/// Name: SYD
144/// Type: SydFn
145/// Min args: 4
146/// Max args: 4
147/// Variadic: false
148/// Signature: SYD(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar)
149/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg4{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
150/// Caps: PURE
151/// [formualizer-docgen:schema:end]
152impl Function for SydFn {
153    func_caps!(PURE);
154    fn name(&self) -> &'static str {
155        "SYD"
156    }
157    fn min_args(&self) -> usize {
158        4
159    }
160    fn arg_schema(&self) -> &'static [ArgSchema] {
161        use std::sync::LazyLock;
162        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
163            vec![
164                ArgSchema::number_lenient_scalar(),
165                ArgSchema::number_lenient_scalar(),
166                ArgSchema::number_lenient_scalar(),
167                ArgSchema::number_lenient_scalar(),
168            ]
169        });
170        &SCHEMA[..]
171    }
172    fn eval<'a, 'b, 'c>(
173        &self,
174        args: &'c [ArgumentHandle<'a, 'b>],
175        _ctx: &dyn FunctionContext<'b>,
176    ) -> Result<CalcValue<'b>, ExcelError> {
177        let cost = coerce_num(&args[0])?;
178        let salvage = coerce_num(&args[1])?;
179        let life = coerce_num(&args[2])?;
180        let per = coerce_num(&args[3])?;
181
182        if life <= 0.0 || per <= 0.0 || per > life {
183            return Ok(CalcValue::Scalar(
184                LiteralValue::Error(ExcelError::new_num()),
185            ));
186        }
187
188        // Sum of years = life * (life + 1) / 2
189        let sum_of_years = life * (life + 1.0) / 2.0;
190
191        // SYD = (cost - salvage) * (life - per + 1) / sum_of_years
192        let depreciation = (cost - salvage) * (life - per + 1.0) / sum_of_years;
193
194        Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)))
195    }
196}
197
198/// Returns fixed-declining-balance depreciation for a specified period.
199///
200/// `DB` computes per-period depreciation using a declining-balance rate and an optional
201/// first-year month proration.
202///
203/// # Remarks
204/// - Parameters: `cost`, `salvage`, `life`, `period`, and optional `month` (default `12`).
205/// - `month` must be in `1..=12`; `life` and `period` must be positive; invalid values return `#NUM!`.
206/// - `life` and `period` are truncated to integers for period checks and iteration.
207/// - The declining rate is rounded to three decimals; if `cost <= 0` or `salvage <= 0`, this implementation uses a rate of `1.0`.
208/// - Returned value is the period depreciation amount (generally positive expense, but sign follows provided inputs).
209///
210/// # Examples
211///
212/// ```yaml,sandbox
213/// title: "First full-year DB period"
214/// formula: "=DB(10000, 1000, 5, 1)"
215/// expected: 3690
216/// ```
217///
218/// ```yaml,sandbox
219/// title: "Fractional period input is truncated"
220/// formula: "=DB(10000, 1000, 5, 2.9)"
221/// expected: 2328.39
222/// ```
223/// ```yaml,docs
224/// related:
225///   - DDB
226///   - SYD
227///   - SLN
228/// faq:
229///   - q: "How is `month` used in `DB`?"
230///     a: "`month` prorates the first-year depreciation; if omitted it defaults to `12`."
231///   - q: "Why can fractional `period` inputs behave like integers?"
232///     a: "`DB` truncates `life` and `period` to integers for iteration and period bounds."
233/// ```
234#[derive(Debug)]
235pub struct DbFn;
236/// [formualizer-docgen:schema:start]
237/// Name: DB
238/// Type: DbFn
239/// Min args: 4
240/// Max args: variadic
241/// Variadic: true
242/// Signature: DB(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5...: number@scalar)
243/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg4{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg5{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
244/// Caps: PURE
245/// [formualizer-docgen:schema:end]
246impl Function for DbFn {
247    func_caps!(PURE);
248    fn name(&self) -> &'static str {
249        "DB"
250    }
251    fn min_args(&self) -> usize {
252        4
253    }
254    fn variadic(&self) -> bool {
255        true
256    }
257    fn arg_schema(&self) -> &'static [ArgSchema] {
258        use std::sync::LazyLock;
259        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
260            vec![
261                ArgSchema::number_lenient_scalar(),
262                ArgSchema::number_lenient_scalar(),
263                ArgSchema::number_lenient_scalar(),
264                ArgSchema::number_lenient_scalar(),
265                ArgSchema::number_lenient_scalar(),
266            ]
267        });
268        &SCHEMA[..]
269    }
270    fn eval<'a, 'b, 'c>(
271        &self,
272        args: &'c [ArgumentHandle<'a, 'b>],
273        _ctx: &dyn FunctionContext<'b>,
274    ) -> Result<CalcValue<'b>, ExcelError> {
275        let cost = coerce_num(&args[0])?;
276        let salvage = coerce_num(&args[1])?;
277        let life = coerce_num(&args[2])?;
278        let period = coerce_num(&args[3])?;
279        let month = if args.len() > 4 {
280            coerce_num(&args[4])?
281        } else {
282            12.0
283        };
284
285        if life <= 0.0 || period <= 0.0 || !(1.0..=12.0).contains(&month) {
286            return Ok(CalcValue::Scalar(
287                LiteralValue::Error(ExcelError::new_num()),
288            ));
289        }
290
291        let life_int = life.trunc() as i32;
292        let period_int = period.trunc() as i32;
293
294        if period_int < 1 || period_int > life_int + 1 {
295            return Ok(CalcValue::Scalar(
296                LiteralValue::Error(ExcelError::new_num()),
297            ));
298        }
299
300        // Calculate rate (rounded to 3 decimal places)
301        let rate = if cost <= 0.0 || salvage <= 0.0 {
302            1.0
303        } else {
304            let r = 1.0 - (salvage / cost).powf(1.0 / life);
305            (r * 1000.0).round() / 1000.0
306        };
307
308        let mut total_depreciation = 0.0;
309        let value = cost;
310
311        for p in 1..=period_int {
312            let depreciation = if p == 1 {
313                // First period: prorated
314                value * rate * month / 12.0
315            } else if p == life_int + 1 {
316                // Last period (if partial year): remaining value minus salvage
317                (value - total_depreciation - salvage)
318                    .max(0.0)
319                    .min(value - total_depreciation)
320            } else {
321                (value - total_depreciation) * rate
322            };
323
324            if p == period_int {
325                return Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)));
326            }
327
328            total_depreciation += depreciation;
329        }
330
331        Ok(CalcValue::Scalar(LiteralValue::Number(0.0)))
332    }
333}
334
335/// Returns declining-balance depreciation for a period using a configurable acceleration factor.
336///
337/// `DDB` defaults to the double-declining method (`factor = 2`) and applies a salvage floor so
338/// book value does not fall below `salvage`.
339///
340/// # Remarks
341/// - Parameters: `cost`, `salvage`, `life`, `period`, and optional `factor` (default `2`).
342/// - Input constraints: `cost >= 0`, `salvage >= 0`, `life > 0`, `period > 0`, `factor > 0`, and `period <= life`; violations return `#NUM!`.
343/// - Per-period rate is `factor / life`.
344/// - This implementation processes the integer part of `period` and then blends with the next period for a fractional remainder.
345/// - Result is the period depreciation amount; with valid inputs above it is non-negative.
346///
347/// # Examples
348///
349/// ```yaml,sandbox
350/// title: "Default double-declining first period"
351/// formula: "=DDB(10000, 1000, 5, 1)"
352/// expected: 4000
353/// ```
354///
355/// ```yaml,sandbox
356/// title: "Using a custom factor"
357/// formula: "=DDB(10000, 1000, 5, 1, 1.5)"
358/// expected: 3000
359/// ```
360/// ```yaml,docs
361/// related:
362///   - DB
363///   - SYD
364///   - SLN
365/// faq:
366///   - q: "What does the optional `factor` control?"
367///     a: "It sets the per-period declining rate as `factor / life`; `2` gives double-declining balance."
368///   - q: "When does `DDB` return `#NUM!`?"
369///     a: "Invalid non-positive inputs (`life`, `period`, `factor`), negative `cost`/`salvage`, or `period > life`."
370/// ```
371#[derive(Debug)]
372pub struct DdbFn;
373/// [formualizer-docgen:schema:start]
374/// Name: DDB
375/// Type: DdbFn
376/// Min args: 4
377/// Max args: variadic
378/// Variadic: true
379/// Signature: DDB(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5...: number@scalar)
380/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg4{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg5{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
381/// Caps: PURE
382/// [formualizer-docgen:schema:end]
383impl Function for DdbFn {
384    func_caps!(PURE);
385    fn name(&self) -> &'static str {
386        "DDB"
387    }
388    fn min_args(&self) -> usize {
389        4
390    }
391    fn variadic(&self) -> bool {
392        true
393    }
394    fn arg_schema(&self) -> &'static [ArgSchema] {
395        use std::sync::LazyLock;
396        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
397            vec![
398                ArgSchema::number_lenient_scalar(),
399                ArgSchema::number_lenient_scalar(),
400                ArgSchema::number_lenient_scalar(),
401                ArgSchema::number_lenient_scalar(),
402                ArgSchema::number_lenient_scalar(),
403            ]
404        });
405        &SCHEMA[..]
406    }
407    fn eval<'a, 'b, 'c>(
408        &self,
409        args: &'c [ArgumentHandle<'a, 'b>],
410        _ctx: &dyn FunctionContext<'b>,
411    ) -> Result<CalcValue<'b>, ExcelError> {
412        let cost = coerce_num(&args[0])?;
413        let salvage = coerce_num(&args[1])?;
414        let life = coerce_num(&args[2])?;
415        let period = coerce_num(&args[3])?;
416        let factor = if args.len() > 4 {
417            coerce_num(&args[4])?
418        } else {
419            2.0
420        };
421
422        if cost < 0.0 || salvage < 0.0 || life <= 0.0 || period <= 0.0 || factor <= 0.0 {
423            return Ok(CalcValue::Scalar(
424                LiteralValue::Error(ExcelError::new_num()),
425            ));
426        }
427
428        if period > life {
429            return Ok(CalcValue::Scalar(
430                LiteralValue::Error(ExcelError::new_num()),
431            ));
432        }
433
434        let rate = factor / life;
435        let mut value = cost;
436        let mut depreciation = 0.0;
437
438        for p in 1..=(period.trunc() as i32) {
439            depreciation = value * rate;
440            // Don't depreciate below salvage value
441            if value - depreciation < salvage {
442                depreciation = (value - salvage).max(0.0);
443            }
444            value -= depreciation;
445        }
446
447        // TODO: Handle fractional period - this logic is incorrect and doesn't match Excel
448        // Excel returns an error for non-integer periods. This weighted average approach
449        // should be removed or replaced with proper error handling.
450        let frac = period.fract();
451        if frac > 0.0 {
452            let next_depreciation = value * rate;
453            let next_depreciation = if value - next_depreciation < salvage {
454                (value - salvage).max(0.0)
455            } else {
456                next_depreciation
457            };
458            depreciation = depreciation * (1.0 - frac) + next_depreciation * frac;
459        }
460
461        Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)))
462    }
463}
464
465pub fn register_builtins() {
466    use std::sync::Arc;
467    crate::function_registry::register_function(Arc::new(SlnFn));
468    crate::function_registry::register_function(Arc::new(SydFn));
469    crate::function_registry::register_function(Arc::new(DbFn));
470    crate::function_registry::register_function(Arc::new(DdbFn));
471}
formualizer_eval/builtins/financial/depreciation.rs

formualizer_eval/builtins/financial/
depreciation.rs
