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`, `factor > 0`, and `1 <= trunc(period) <= life`; violations return `#NUM!`.
343/// - Per-period rate is `factor / life`.
344/// - `period` is truncated to an integer before calculation, matching Excel behavior.
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///
361/// ```yaml,sandbox
362/// title: "Fractional period is truncated to integer"
363/// formula: "=DDB(10000, 1000, 5, 1.9)"
364/// expected: 4000
365/// ```
366/// ```yaml,docs
367/// related:
368///   - DB
369///   - SYD
370///   - SLN
371/// faq:
372///   - q: "What does the optional `factor` control?"
373///     a: "It sets the per-period declining rate as `factor / life`; `2` gives double-declining balance."
374///   - q: "When does `DDB` return `#NUM!`?"
375///     a: "Invalid non-positive inputs (`life`, `period`, `factor`), negative `cost`/`salvage`, or `period > life`."
376///   - q: "What happens with a fractional `period`?"
377///     a: "`period` is truncated to an integer before calculation (e.g. `1.9` is treated as `1`), matching Excel and DB behavior."
378/// ```
379#[derive(Debug)]
380pub struct DdbFn;
381/// [formualizer-docgen:schema:start]
382/// Name: DDB
383/// Type: DdbFn
384/// Min args: 4
385/// Max args: variadic
386/// Variadic: true
387/// Signature: DDB(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5...: number@scalar)
388/// 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}
389/// Caps: PURE
390/// [formualizer-docgen:schema:end]
391impl Function for DdbFn {
392    func_caps!(PURE);
393    fn name(&self) -> &'static str {
394        "DDB"
395    }
396    fn min_args(&self) -> usize {
397        4
398    }
399    fn variadic(&self) -> bool {
400        true
401    }
402    fn arg_schema(&self) -> &'static [ArgSchema] {
403        use std::sync::LazyLock;
404        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
405            vec![
406                ArgSchema::number_lenient_scalar(),
407                ArgSchema::number_lenient_scalar(),
408                ArgSchema::number_lenient_scalar(),
409                ArgSchema::number_lenient_scalar(),
410                ArgSchema::number_lenient_scalar(),
411            ]
412        });
413        &SCHEMA[..]
414    }
415    fn eval<'a, 'b, 'c>(
416        &self,
417        args: &'c [ArgumentHandle<'a, 'b>],
418        _ctx: &dyn FunctionContext<'b>,
419    ) -> Result<CalcValue<'b>, ExcelError> {
420        let cost = coerce_num(&args[0])?;
421        let salvage = coerce_num(&args[1])?;
422        let life = coerce_num(&args[2])?;
423        let period = coerce_num(&args[3])?;
424        let factor = if args.len() > 4 {
425            coerce_num(&args[4])?
426        } else {
427            2.0
428        };
429
430        if cost < 0.0 || salvage < 0.0 || life <= 0.0 || period <= 0.0 || factor <= 0.0 {
431            return Ok(CalcValue::Scalar(
432                LiteralValue::Error(ExcelError::new_num()),
433            ));
434        }
435
436        // Truncate period to integer, matching Excel and the sibling DB function.
437        let period_int = period.trunc() as i32;
438
439        if period_int < 1 || period_int as f64 > life {
440            return Ok(CalcValue::Scalar(
441                LiteralValue::Error(ExcelError::new_num()),
442            ));
443        }
444
445        let rate = factor / life;
446        let mut value = cost;
447        let mut depreciation = 0.0;
448
449        for _p in 1..=period_int {
450            depreciation = value * rate;
451            // Don't depreciate below salvage value
452            if value - depreciation < salvage {
453                depreciation = (value - salvage).max(0.0);
454            }
455            value -= depreciation;
456        }
457
458        Ok(CalcValue::Scalar(LiteralValue::Number(depreciation)))
459    }
460}
461
462pub fn register_builtins() {
463    use std::sync::Arc;
464    crate::function_registry::register_function(Arc::new(SlnFn));
465    crate::function_registry::register_function(Arc::new(SydFn));
466    crate::function_registry::register_function(Arc::new(DbFn));
467    crate::function_registry::register_function(Arc::new(DdbFn));
468}
formualizer_eval/builtins/financial/depreciation.rs

formualizer_eval/builtins/financial/
depreciation.rs
