Skip to main content

sheetkit_core/formula/functions/
financial.rs

1//! Financial formula functions: FV, PV, NPV, IRR, PMT, IPMT, PPMT, RATE, NPER,
2//! DB, DDB, SLN, SYD, EFFECT, NOMINAL, DOLLARDE, DOLLARFR, CUMPRINC, CUMIPMT,
3//! XNPV, XIRR.
4
5use crate::cell::CellValue;
6use crate::error::Result;
7use crate::formula::ast::Expr;
8use crate::formula::eval::{coerce_to_number, Evaluator};
9use crate::formula::functions::conversion::checked_trunc_i32;
10use crate::formula::functions::{check_arg_count, finite_number_or_num_error};
11
12/// Evaluate the optional integer argument at `index`, truncated toward zero.
13///
14/// Returns `default` when the argument is omitted and `None` when the value is
15/// non-finite or outside the `i32` range.
16fn optional_i32_arg(
17    args: &[Expr],
18    index: usize,
19    default: i32,
20    ctx: &mut Evaluator,
21) -> Result<Option<i32>> {
22    if args.len() <= index {
23        return Ok(Some(default));
24    }
25    let value = coerce_to_number(&ctx.eval_expr(&args[index])?)?;
26    Ok(checked_trunc_i32(value))
27}
28
29/// FV(rate, nper, pmt, \[pv\], \[type\])
30pub fn fn_fv(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
31    check_arg_count("FV", args, 3, 5)?;
32    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
33    let nper = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
34    let pmt = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
35    let pv = if args.len() > 3 {
36        coerce_to_number(&ctx.eval_expr(&args[3])?)?
37    } else {
38        0.0
39    };
40    let Some(pmt_type) = optional_i32_arg(args, 4, 0, ctx)? else {
41        return Ok(CellValue::Error("#NUM!".to_string()));
42    };
43    let fv = calc_fv(rate, nper, pmt, pv, pmt_type);
44    Ok(CellValue::Number(fv))
45}
46
47/// PV(rate, nper, pmt, \[fv\], \[type\])
48pub fn fn_pv(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
49    check_arg_count("PV", args, 3, 5)?;
50    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
51    let nper = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
52    let pmt = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
53    let fv = if args.len() > 3 {
54        coerce_to_number(&ctx.eval_expr(&args[3])?)?
55    } else {
56        0.0
57    };
58    let Some(pmt_type) = optional_i32_arg(args, 4, 0, ctx)? else {
59        return Ok(CellValue::Error("#NUM!".to_string()));
60    };
61    let pv = calc_pv(rate, nper, pmt, fv, pmt_type);
62    Ok(CellValue::Number(pv))
63}
64
65/// NPV(rate, value1, \[value2\], ...)
66pub fn fn_npv(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
67    check_arg_count("NPV", args, 2, 255)?;
68    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
69    let values = ctx.collect_numbers(&args[1..])?;
70    let mut npv = 0.0;
71    for (i, v) in values.iter().enumerate() {
72        npv += v / (1.0 + rate).powi(i as i32 + 1);
73    }
74    Ok(CellValue::Number(npv))
75}
76
77/// IRR(values, \[guess\])
78pub fn fn_irr(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
79    check_arg_count("IRR", args, 1, 2)?;
80    let values = ctx.collect_numbers(&args[0..1])?;
81    let guess = if args.len() > 1 {
82        coerce_to_number(&ctx.eval_expr(&args[1])?)?
83    } else {
84        0.1
85    };
86    match calc_irr(&values, guess) {
87        Some(irr) => Ok(CellValue::Number(irr)),
88        None => Ok(CellValue::Error("#NUM!".to_string())),
89    }
90}
91
92/// PMT(rate, nper, pv, \[fv\], \[type\])
93pub fn fn_pmt(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
94    check_arg_count("PMT", args, 3, 5)?;
95    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
96    let nper = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
97    let pv = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
98    let fv = if args.len() > 3 {
99        coerce_to_number(&ctx.eval_expr(&args[3])?)?
100    } else {
101        0.0
102    };
103    let Some(pmt_type) = optional_i32_arg(args, 4, 0, ctx)? else {
104        return Ok(CellValue::Error("#NUM!".to_string()));
105    };
106    let pmt = calc_pmt(rate, nper, pv, fv, pmt_type);
107    Ok(CellValue::Number(pmt))
108}
109
110/// IPMT(rate, per, nper, pv, \[fv\], \[type\])
111pub fn fn_ipmt(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
112    check_arg_count("IPMT", args, 4, 6)?;
113    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
114    let per = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
115    let nper = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
116    let pv = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
117    let fv = if args.len() > 4 {
118        coerce_to_number(&ctx.eval_expr(&args[4])?)?
119    } else {
120        0.0
121    };
122    let Some(pmt_type) = optional_i32_arg(args, 5, 0, ctx)? else {
123        return Ok(CellValue::Error("#NUM!".to_string()));
124    };
125    if per < 1.0 || per > nper {
126        return Ok(CellValue::Error("#NUM!".to_string()));
127    }
128    let ipmt = calc_ipmt(rate, per, nper, pv, fv, pmt_type);
129    Ok(CellValue::Number(ipmt))
130}
131
132/// PPMT(rate, per, nper, pv, \[fv\], \[type\])
133pub fn fn_ppmt(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
134    check_arg_count("PPMT", args, 4, 6)?;
135    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
136    let per = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
137    let nper = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
138    let pv = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
139    let fv = if args.len() > 4 {
140        coerce_to_number(&ctx.eval_expr(&args[4])?)?
141    } else {
142        0.0
143    };
144    let Some(pmt_type) = optional_i32_arg(args, 5, 0, ctx)? else {
145        return Ok(CellValue::Error("#NUM!".to_string()));
146    };
147    if per < 1.0 || per > nper {
148        return Ok(CellValue::Error("#NUM!".to_string()));
149    }
150    let pmt = calc_pmt(rate, nper, pv, fv, pmt_type);
151    let ipmt = calc_ipmt(rate, per, nper, pv, fv, pmt_type);
152    Ok(CellValue::Number(pmt - ipmt))
153}
154
155/// RATE(nper, pmt, pv, \[fv\], \[type\], \[guess\])
156pub fn fn_rate(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
157    check_arg_count("RATE", args, 3, 6)?;
158    let nper = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
159    let pmt = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
160    let pv = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
161    let fv = if args.len() > 3 {
162        coerce_to_number(&ctx.eval_expr(&args[3])?)?
163    } else {
164        0.0
165    };
166    let Some(pmt_type) = optional_i32_arg(args, 4, 0, ctx)? else {
167        return Ok(CellValue::Error("#NUM!".to_string()));
168    };
169    let guess = if args.len() > 5 {
170        coerce_to_number(&ctx.eval_expr(&args[5])?)?
171    } else {
172        0.1
173    };
174    match calc_rate(nper, pmt, pv, fv, pmt_type, guess) {
175        Some(rate) => Ok(CellValue::Number(rate)),
176        None => Ok(CellValue::Error("#NUM!".to_string())),
177    }
178}
179
180/// NPER(rate, pmt, pv, \[fv\], \[type\])
181pub fn fn_nper(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
182    check_arg_count("NPER", args, 3, 5)?;
183    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
184    let pmt = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
185    let pv = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
186    let fv = if args.len() > 3 {
187        coerce_to_number(&ctx.eval_expr(&args[3])?)?
188    } else {
189        0.0
190    };
191    let Some(pmt_type) = optional_i32_arg(args, 4, 0, ctx)? else {
192        return Ok(CellValue::Error("#NUM!".to_string()));
193    };
194    if rate == 0.0 {
195        if pmt == 0.0 {
196            return Ok(CellValue::Error("#NUM!".to_string()));
197        }
198        return Ok(CellValue::Number(-(pv + fv) / pmt));
199    }
200    let z = pmt * (1.0 + rate * pmt_type as f64) / rate;
201    let numer = -fv + z;
202    let denom = pv + z;
203    if numer <= 0.0 && denom <= 0.0 {
204        return Ok(CellValue::Number((numer / denom).ln() / (1.0 + rate).ln()));
205    }
206    if numer / denom <= 0.0 {
207        return Ok(CellValue::Error("#NUM!".to_string()));
208    }
209    Ok(CellValue::Number((numer / denom).ln() / (1.0 + rate).ln()))
210}
211
212/// DB(cost, salvage, life, period, \[month\])
213pub fn fn_db(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
214    check_arg_count("DB", args, 4, 5)?;
215    let cost = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
216    let salvage = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
217    let life_value = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
218    let period_value = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
219    let month_value = if args.len() > 4 {
220        coerce_to_number(&ctx.eval_expr(&args[4])?)?
221    } else {
222        12.0
223    };
224    let (Some(life), Some(period), Some(month)) = (
225        checked_trunc_i32(life_value),
226        checked_trunc_i32(period_value),
227        checked_trunc_i32(month_value),
228    ) else {
229        return Ok(CellValue::Error("#NUM!".to_string()));
230    };
231    let Some(last_period) = life.checked_add(1) else {
232        return Ok(CellValue::Error("#NUM!".to_string()));
233    };
234    if life <= 0
235        || period <= 0
236        || period > last_period
237        || !cost.is_finite()
238        || !salvage.is_finite()
239        || cost < 0.0
240        || salvage < 0.0
241        || !(1..=12).contains(&month)
242    {
243        return Ok(CellValue::Error("#NUM!".to_string()));
244    }
245    if cost == 0.0 {
246        return Ok(CellValue::Number(0.0));
247    }
248    let rate = (1.0 - (salvage / cost).powf(1.0 / life as f64)) * 1000.0;
249    let rate = rate.round() / 1000.0;
250    if !rate.is_finite() {
251        return Ok(CellValue::Error("#NUM!".to_string()));
252    }
253    ctx.charge_evaluation_work(u64::try_from(period.saturating_sub(2).max(0)).unwrap_or(u64::MAX))?;
254    let mut total_depreciation = 0.0;
255    let first_year_dep = cost * rate * month as f64 / 12.0;
256    if !first_year_dep.is_finite() {
257        return Ok(CellValue::Error("#NUM!".to_string()));
258    }
259    if period == 1 {
260        return Ok(CellValue::Number(first_year_dep));
261    }
262    total_depreciation += first_year_dep;
263    for p in 2..period {
264        let dep = (cost - total_depreciation) * rate;
265        total_depreciation += dep;
266        if !dep.is_finite() || !total_depreciation.is_finite() {
267            return Ok(CellValue::Error("#NUM!".to_string()));
268        }
269        if p > life {
270            break;
271        }
272    }
273    if period == last_period {
274        let dep = (cost - total_depreciation) * rate * (12 - month) as f64 / 12.0;
275        return finite_number_or_num_error(dep);
276    }
277    let dep = (cost - total_depreciation) * rate;
278    finite_number_or_num_error(dep)
279}
280
281/// DDB(cost, salvage, life, period, \[factor\])
282pub fn fn_ddb(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
283    check_arg_count("DDB", args, 4, 5)?;
284    let cost = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
285    let salvage = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
286    let life = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
287    let period = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
288    let factor = if args.len() > 4 {
289        coerce_to_number(&ctx.eval_expr(&args[4])?)?
290    } else {
291        2.0
292    };
293    let Some(period_count) = checked_trunc_i32(period) else {
294        return Ok(CellValue::Error("#NUM!".to_string()));
295    };
296    if !cost.is_finite()
297        || !salvage.is_finite()
298        || !life.is_finite()
299        || !factor.is_finite()
300        || life <= 0.0
301        || period <= 0.0
302        || period > life
303        || cost < 0.0
304        || salvage < 0.0
305        || factor <= 0.0
306    {
307        return Ok(CellValue::Error("#NUM!".to_string()));
308    }
309    let rate = factor / life;
310    let rate = rate.min(1.0);
311    if !rate.is_finite() {
312        return Ok(CellValue::Error("#NUM!".to_string()));
313    }
314    ctx.charge_evaluation_work(u64::try_from(period_count).unwrap_or(u64::MAX))?;
315    let mut total_dep = 0.0;
316    for p in 1..=period_count {
317        let current_value = cost - total_dep;
318        let mut dep = current_value * rate;
319        if current_value - dep < salvage {
320            dep = (current_value - salvage).max(0.0);
321        }
322        if !dep.is_finite() {
323            return Ok(CellValue::Error("#NUM!".to_string()));
324        }
325        if p == period_count {
326            return Ok(CellValue::Number(dep));
327        }
328        total_dep += dep;
329        if !total_dep.is_finite() {
330            return Ok(CellValue::Error("#NUM!".to_string()));
331        }
332    }
333    Ok(CellValue::Number(0.0))
334}
335
336/// SLN(cost, salvage, life)
337pub fn fn_sln(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
338    check_arg_count("SLN", args, 3, 3)?;
339    let cost = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
340    let salvage = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
341    let life = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
342    if life == 0.0 {
343        return Ok(CellValue::Error("#DIV/0!".to_string()));
344    }
345    Ok(CellValue::Number((cost - salvage) / life))
346}
347
348/// SYD(cost, salvage, life, per)
349pub fn fn_syd(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
350    check_arg_count("SYD", args, 4, 4)?;
351    let cost = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
352    let salvage = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
353    let life = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
354    let per = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
355    if life <= 0.0 || per <= 0.0 || per > life {
356        return Ok(CellValue::Error("#NUM!".to_string()));
357    }
358    let sum_of_years = life * (life + 1.0) / 2.0;
359    let dep = (cost - salvage) * (life - per + 1.0) / sum_of_years;
360    Ok(CellValue::Number(dep))
361}
362
363/// EFFECT(nominal_rate, npery)
364pub fn fn_effect(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
365    check_arg_count("EFFECT", args, 2, 2)?;
366    let nominal = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
367    let Some(npery) = optional_i32_arg(args, 1, 0, ctx)? else {
368        return Ok(CellValue::Error("#NUM!".to_string()));
369    };
370    if nominal <= 0.0 || npery < 1 {
371        return Ok(CellValue::Error("#NUM!".to_string()));
372    }
373    let result = (1.0 + nominal / npery as f64).powi(npery) - 1.0;
374    Ok(CellValue::Number(result))
375}
376
377/// NOMINAL(effect_rate, npery)
378pub fn fn_nominal(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
379    check_arg_count("NOMINAL", args, 2, 2)?;
380    let effect = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
381    let Some(npery) = optional_i32_arg(args, 1, 0, ctx)? else {
382        return Ok(CellValue::Error("#NUM!".to_string()));
383    };
384    if effect <= 0.0 || npery < 1 {
385        return Ok(CellValue::Error("#NUM!".to_string()));
386    }
387    let result = npery as f64 * ((1.0 + effect).powf(1.0 / npery as f64) - 1.0);
388    Ok(CellValue::Number(result))
389}
390
391/// DOLLARDE(fractional_dollar, fraction)
392pub fn fn_dollarde(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
393    check_arg_count("DOLLARDE", args, 2, 2)?;
394    let fractional = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
395    let Some(fraction) = optional_i32_arg(args, 1, 0, ctx)? else {
396        return Ok(CellValue::Error("#NUM!".to_string()));
397    };
398    if fraction < 1 {
399        return Ok(CellValue::Error("#NUM!".to_string()));
400    }
401    let sign = if fractional < 0.0 { -1.0 } else { 1.0 };
402    let abs_val = fractional.abs();
403    let int_part = abs_val.floor();
404    let frac_part = abs_val - int_part;
405    let digits = (fraction as f64).log10().ceil().max(1.0) as u32;
406    let power = 10f64.powi(digits as i32);
407    let frac_decimal = frac_part * power / fraction as f64;
408    Ok(CellValue::Number(sign * (int_part + frac_decimal)))
409}
410
411/// DOLLARFR(decimal_dollar, fraction)
412pub fn fn_dollarfr(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
413    check_arg_count("DOLLARFR", args, 2, 2)?;
414    let decimal = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
415    let Some(fraction) = optional_i32_arg(args, 1, 0, ctx)? else {
416        return Ok(CellValue::Error("#NUM!".to_string()));
417    };
418    if fraction < 1 {
419        return Ok(CellValue::Error("#NUM!".to_string()));
420    }
421    let sign = if decimal < 0.0 { -1.0 } else { 1.0 };
422    let abs_val = decimal.abs();
423    let int_part = abs_val.floor();
424    let frac_part = abs_val - int_part;
425    let digits = (fraction as f64).log10().ceil().max(1.0) as u32;
426    let power = 10f64.powi(digits as i32);
427    let frac_result = frac_part * fraction as f64 / power;
428    Ok(CellValue::Number(sign * (int_part + frac_result)))
429}
430
431/// CUMIPMT(rate, nper, pv, start_period, end_period, type)
432pub fn fn_cumipmt(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
433    check_arg_count("CUMIPMT", args, 6, 6)?;
434    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
435    let nper = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
436    let pv = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
437    let start_value = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
438    let end_value = coerce_to_number(&ctx.eval_expr(&args[4])?)?;
439    let pmt_type_value = coerce_to_number(&ctx.eval_expr(&args[5])?)?;
440    let (Some(start), Some(end), Some(pmt_type)) = (
441        checked_trunc_i32(start_value),
442        checked_trunc_i32(end_value),
443        checked_trunc_i32(pmt_type_value),
444    ) else {
445        return Ok(CellValue::Error("#NUM!".to_string()));
446    };
447    if !rate.is_finite()
448        || !nper.is_finite()
449        || !pv.is_finite()
450        || rate <= 0.0
451        || nper <= 0.0
452        || pv <= 0.0
453        || start < 1
454        || end < 1
455        || start > end
456        || f64::from(end) > nper
457        || (pmt_type != 0 && pmt_type != 1)
458    {
459        return Ok(CellValue::Error("#NUM!".to_string()));
460    }
461    let Some(period_count) = i64::from(end)
462        .checked_sub(i64::from(start))
463        .and_then(|distance| distance.checked_add(1))
464        .and_then(|count| u64::try_from(count).ok())
465    else {
466        return Ok(CellValue::Error("#NUM!".to_string()));
467    };
468    ctx.charge_evaluation_work(period_count)?;
469    let mut total = 0.0;
470    for per in start..=end {
471        let interest = calc_ipmt(rate, per as f64, nper, pv, 0.0, pmt_type);
472        if !interest.is_finite() {
473            return Ok(CellValue::Error("#NUM!".to_string()));
474        }
475        total += interest;
476        if !total.is_finite() {
477            return Ok(CellValue::Error("#NUM!".to_string()));
478        }
479    }
480    Ok(CellValue::Number(total))
481}
482
483/// CUMPRINC(rate, nper, pv, start_period, end_period, type)
484pub fn fn_cumprinc(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
485    check_arg_count("CUMPRINC", args, 6, 6)?;
486    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
487    let nper = coerce_to_number(&ctx.eval_expr(&args[1])?)?;
488    let pv = coerce_to_number(&ctx.eval_expr(&args[2])?)?;
489    let start_value = coerce_to_number(&ctx.eval_expr(&args[3])?)?;
490    let end_value = coerce_to_number(&ctx.eval_expr(&args[4])?)?;
491    let pmt_type_value = coerce_to_number(&ctx.eval_expr(&args[5])?)?;
492    let period_values_are_valid = start_value.is_finite()
493        && end_value.is_finite()
494        && start_value >= 1.0
495        && end_value >= 1.0
496        && start_value <= f64::from(i32::MAX)
497        && end_value <= f64::from(i32::MAX);
498    let payment_type_is_valid = pmt_type_value.is_finite()
499        && pmt_type_value >= f64::from(i32::MIN)
500        && pmt_type_value <= f64::from(i32::MAX);
501    if !period_values_are_valid || !payment_type_is_valid {
502        return Ok(CellValue::Error("#NUM!".to_string()));
503    }
504    let start = start_value as i32;
505    let end = end_value as i32;
506    let pmt_type = pmt_type_value as i32;
507    if rate <= 0.0
508        || !rate.is_finite()
509        || nper <= 0.0
510        || !nper.is_finite()
511        || pv <= 0.0
512        || !pv.is_finite()
513        || start > end
514        || f64::from(end) > nper
515        || (pmt_type != 0 && pmt_type != 1)
516    {
517        return Ok(CellValue::Error("#NUM!".to_string()));
518    }
519    let Some(period_count) = i64::from(end)
520        .checked_sub(i64::from(start))
521        .and_then(|distance| distance.checked_add(1))
522        .and_then(|count| u64::try_from(count).ok())
523    else {
524        return Ok(CellValue::Error("#NUM!".to_string()));
525    };
526    ctx.charge_evaluation_work(period_count)?;
527    let pmt = calc_pmt(rate, nper, pv, 0.0, pmt_type);
528    if !pmt.is_finite() {
529        return Ok(CellValue::Error("#NUM!".to_string()));
530    }
531    let mut total = 0.0;
532    for per in start..=end {
533        let ipmt = calc_ipmt(rate, per as f64, nper, pv, 0.0, pmt_type);
534        let principal = pmt - ipmt;
535        if !ipmt.is_finite() || !principal.is_finite() {
536            return Ok(CellValue::Error("#NUM!".to_string()));
537        }
538        total += principal;
539        if !total.is_finite() {
540            return Ok(CellValue::Error("#NUM!".to_string()));
541        }
542    }
543    Ok(CellValue::Number(total))
544}
545
546/// XNPV(rate, values, dates)
547pub fn fn_xnpv(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
548    check_arg_count("XNPV", args, 3, 3)?;
549    let rate = coerce_to_number(&ctx.eval_expr(&args[0])?)?;
550    let values = ctx.collect_numbers(&args[1..2])?;
551    let dates = ctx.collect_numbers(&args[2..3])?;
552    if values.len() != dates.len() || values.is_empty() {
553        return Ok(CellValue::Error("#NUM!".to_string()));
554    }
555    if rate <= -1.0 {
556        return Ok(CellValue::Error("#NUM!".to_string()));
557    }
558    let d0 = dates[0];
559    let mut npv = 0.0;
560    for (i, v) in values.iter().enumerate() {
561        let days = dates[i] - d0;
562        npv += v / (1.0 + rate).powf(days / 365.0);
563    }
564    Ok(CellValue::Number(npv))
565}
566
567/// XIRR(values, dates, \[guess\])
568pub fn fn_xirr(args: &[Expr], ctx: &mut Evaluator) -> Result<CellValue> {
569    check_arg_count("XIRR", args, 2, 3)?;
570    let values = ctx.collect_numbers(&args[0..1])?;
571    let dates = ctx.collect_numbers(&args[1..2])?;
572    let guess = if args.len() > 2 {
573        coerce_to_number(&ctx.eval_expr(&args[2])?)?
574    } else {
575        0.1
576    };
577    if values.len() != dates.len() || values.len() < 2 {
578        return Ok(CellValue::Error("#NUM!".to_string()));
579    }
580    let has_positive = values.iter().any(|v| *v > 0.0);
581    let has_negative = values.iter().any(|v| *v < 0.0);
582    if !has_positive || !has_negative {
583        return Ok(CellValue::Error("#NUM!".to_string()));
584    }
585    match calc_xirr(&values, &dates, guess) {
586        Some(irr) => Ok(CellValue::Number(irr)),
587        None => Ok(CellValue::Error("#NUM!".to_string())),
588    }
589}
590
591fn calc_fv(rate: f64, nper: f64, pmt: f64, pv: f64, pmt_type: i32) -> f64 {
592    if rate == 0.0 {
593        return -(pv + pmt * nper);
594    }
595    let pow = (1.0 + rate).powf(nper);
596    let factor = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
597    -(pv * pow + pmt * factor * (pow - 1.0) / rate)
598}
599
600fn calc_pv(rate: f64, nper: f64, pmt: f64, fv: f64, pmt_type: i32) -> f64 {
601    if rate == 0.0 {
602        return -(fv + pmt * nper);
603    }
604    let pow = (1.0 + rate).powf(nper);
605    let factor = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
606    -(fv + pmt * factor * (pow - 1.0) / rate) / pow
607}
608
609fn calc_pmt(rate: f64, nper: f64, pv: f64, fv: f64, pmt_type: i32) -> f64 {
610    if rate == 0.0 {
611        return -(pv + fv) / nper;
612    }
613    let pow = (1.0 + rate).powf(nper);
614    let factor = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
615    -(pv * pow + fv) / (factor * (pow - 1.0) / rate)
616}
617
618fn calc_ipmt(rate: f64, per: f64, nper: f64, pv: f64, fv: f64, pmt_type: i32) -> f64 {
619    let pmt = calc_pmt(rate, nper, pv, fv, pmt_type);
620    if pmt_type != 0 {
621        if per == 1.0 {
622            return 0.0;
623        }
624        let fv_prev = calc_fv(rate, per - 1.0, pmt, pv, pmt_type);
625        fv_prev * rate / (1.0 + rate)
626    } else {
627        let fv_prev = calc_fv(rate, per - 1.0, pmt, pv, pmt_type);
628        fv_prev * rate
629    }
630}
631
632fn calc_irr(values: &[f64], guess: f64) -> Option<f64> {
633    let has_positive = values.iter().any(|v| *v > 0.0);
634    let has_negative = values.iter().any(|v| *v < 0.0);
635    if !has_positive || !has_negative {
636        return None;
637    }
638    let mut rate = guess;
639    for _ in 0..100 {
640        let mut npv = 0.0;
641        let mut dnpv = 0.0;
642        for (i, v) in values.iter().enumerate() {
643            let denom = (1.0 + rate).powi(i as i32);
644            if denom == 0.0 {
645                return None;
646            }
647            npv += v / denom;
648            dnpv -= (i as f64) * v / (1.0 + rate).powi(i as i32 + 1);
649        }
650        if dnpv.abs() < 1e-15 {
651            return None;
652        }
653        let new_rate = rate - npv / dnpv;
654        if (new_rate - rate).abs() < 1e-10 {
655            return Some(new_rate);
656        }
657        rate = new_rate;
658    }
659    None
660}
661
662fn calc_rate(nper: f64, pmt: f64, pv: f64, fv: f64, pmt_type: i32, guess: f64) -> Option<f64> {
663    let mut rate = guess;
664    for _ in 0..100 {
665        if rate <= -1.0 {
666            return None;
667        }
668        let pow = (1.0 + rate).powf(nper);
669        let factor = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
670        let y = pv * pow + pmt * factor * (pow - 1.0) / rate + fv;
671        let dy = pv * nper * (1.0 + rate).powf(nper - 1.0)
672            + pmt
673                * (factor * nper * (1.0 + rate).powf(nper - 1.0) / rate
674                    - factor * (pow - 1.0) / (rate * rate)
675                    + if pmt_type != 0 {
676                        (pow - 1.0) / rate
677                    } else {
678                        0.0
679                    });
680        if dy.abs() < 1e-15 {
681            return None;
682        }
683        let new_rate = rate - y / dy;
684        if (new_rate - rate).abs() < 1e-10 {
685            return Some(new_rate);
686        }
687        rate = new_rate;
688    }
689    None
690}
691
692fn calc_xirr(values: &[f64], dates: &[f64], guess: f64) -> Option<f64> {
693    let d0 = dates[0];
694    let mut rate = guess;
695    for _ in 0..100 {
696        let mut npv = 0.0;
697        let mut dnpv = 0.0;
698        for (i, v) in values.iter().enumerate() {
699            let days = (dates[i] - d0) / 365.0;
700            let denom = (1.0 + rate).powf(days);
701            if denom == 0.0 || !denom.is_finite() {
702                return None;
703            }
704            npv += v / denom;
705            dnpv -= days * v / ((1.0 + rate).powf(days + 1.0));
706        }
707        if dnpv.abs() < 1e-15 {
708            return None;
709        }
710        let new_rate = rate - npv / dnpv;
711        if (new_rate - rate).abs() < 1e-10 {
712            return Some(new_rate);
713        }
714        rate = new_rate;
715    }
716    None
717}
718
719#[cfg(test)]
720mod tests {
721    use crate::cell::CellValue;
722    use crate::formula::eval::{evaluate, CellSnapshot};
723    use crate::formula::parser::parse_formula;
724
725    fn eval_with_data(formula: &str, data: &[(&str, u32, u32, CellValue)]) -> CellValue {
726        let mut snap = CellSnapshot::new("Sheet1".to_string());
727        for (sheet, col, row, val) in data {
728            snap.set_cell(sheet, *col, *row, val.clone());
729        }
730        let expr = parse_formula(formula).unwrap();
731        evaluate(&expr, &snap).unwrap()
732    }
733
734    fn eval(formula: &str) -> CellValue {
735        eval_with_data(formula, &[])
736    }
737
738    fn assert_approx(result: CellValue, expected: f64, tol: f64) {
739        match result {
740            CellValue::Number(n) => {
741                assert!((n - expected).abs() < tol, "expected ~{expected}, got {n}");
742            }
743            other => panic!("expected number ~{expected}, got {other:?}"),
744        }
745    }
746
747    #[test]
748    fn fv_basic() {
749        assert_approx(eval("FV(0.06/12,10,-200,-500,1)"), 2581.4033740601, 0.01);
750    }
751
752    #[test]
753    fn fv_zero_rate() {
754        assert_approx(eval("FV(0,10,-200)"), 2000.0, 0.01);
755    }
756
757    #[test]
758    fn pv_basic() {
759        assert_approx(eval("PV(0.08/12,20*12,500,0,0)"), -59777.1458, 0.01);
760    }
761
762    #[test]
763    fn npv_basic() {
764        let data = vec![
765            ("Sheet1", 1, 1, CellValue::Number(-10000.0)),
766            ("Sheet1", 1, 2, CellValue::Number(3000.0)),
767            ("Sheet1", 1, 3, CellValue::Number(4200.0)),
768            ("Sheet1", 1, 4, CellValue::Number(6800.0)),
769        ];
770        assert_approx(eval_with_data("NPV(0.1,A1:A4)", &data), 1188.4434, 0.01);
771    }
772
773    #[test]
774    fn irr_basic() {
775        let data = vec![
776            ("Sheet1", 1, 1, CellValue::Number(-70000.0)),
777            ("Sheet1", 1, 2, CellValue::Number(12000.0)),
778            ("Sheet1", 1, 3, CellValue::Number(15000.0)),
779            ("Sheet1", 1, 4, CellValue::Number(18000.0)),
780            ("Sheet1", 1, 5, CellValue::Number(21000.0)),
781            ("Sheet1", 1, 6, CellValue::Number(26000.0)),
782        ];
783        assert_approx(eval_with_data("IRR(A1:A6)", &data), 0.08663, 0.001);
784    }
785
786    #[test]
787    fn irr_no_sign_change() {
788        let data = vec![
789            ("Sheet1", 1, 1, CellValue::Number(100.0)),
790            ("Sheet1", 1, 2, CellValue::Number(200.0)),
791        ];
792        assert_eq!(
793            eval_with_data("IRR(A1:A2)", &data),
794            CellValue::Error("#NUM!".to_string())
795        );
796    }
797
798    #[test]
799    fn pmt_basic() {
800        assert_approx(eval("PMT(0.08/12,10,10000)"), -1037.0321, 0.01);
801    }
802
803    #[test]
804    fn pmt_zero_rate() {
805        assert_approx(eval("PMT(0,10,10000)"), -1000.0, 0.01);
806    }
807
808    #[test]
809    fn ipmt_basic() {
810        assert_approx(eval("IPMT(0.1/12,1,36,8000)"), -66.6667, 0.01);
811    }
812
813    #[test]
814    fn ipmt_invalid_period() {
815        assert_eq!(
816            eval("IPMT(0.1,0,36,8000)"),
817            CellValue::Error("#NUM!".to_string())
818        );
819    }
820
821    #[test]
822    fn ppmt_basic() {
823        let pmt = eval("PMT(0.1/12,36,8000)");
824        let ipmt = eval("IPMT(0.1/12,1,36,8000)");
825        let ppmt = eval("PPMT(0.1/12,1,36,8000)");
826        if let (CellValue::Number(p), CellValue::Number(i), CellValue::Number(pp)) =
827            (pmt, ipmt, ppmt)
828        {
829            assert!((p - i - pp).abs() < 0.01);
830        } else {
831            panic!("expected numbers");
832        }
833    }
834
835    #[test]
836    fn ipmt_and_ppmt_follow_payment_timing_schedules() {
837        // Excel's IPMT documentation example: 10% annual rate paid monthly.
838        let rate = 0.1 / 12.0;
839        for (payment_type, expected_interest) in [
840            (0, [-8.3333333333, -5.5785756372, -2.8008616270]),
841            (1, [0.0, -5.5324717063, -2.7777140102]),
842        ] {
843            let pmt = eval(&format!("PMT({rate},3,1000,0,{payment_type})"));
844            let CellValue::Number(pmt) = pmt else {
845                panic!("PMT should return a number");
846            };
847            for (index, expected) in expected_interest.into_iter().enumerate() {
848                let per = index + 1;
849                let ipmt = eval(&format!("IPMT({rate},{per},3,1000,0,{payment_type})"));
850                let ppmt = eval(&format!("PPMT({rate},{per},3,1000,0,{payment_type})"));
851                assert_approx(ipmt.clone(), expected, 1e-8);
852                let (CellValue::Number(ipmt), CellValue::Number(ppmt)) = (ipmt, ppmt) else {
853                    panic!("IPMT and PPMT should return numbers");
854                };
855                assert!((pmt - ipmt - ppmt).abs() < 1e-8);
856            }
857        }
858    }
859
860    #[test]
861    fn beginning_of_period_cumulative_interest_and_principal_match_schedule() {
862        let rate = 0.1 / 12.0;
863        let cumulative_interest = eval(&format!("CUMIPMT({rate},3,1000,1,3,1)"));
864        let cumulative_principal = eval(&format!("CUMPRINC({rate},3,1000,1,3,1)"));
865        assert_approx(cumulative_interest, -8.3101857166, 1e-8);
866        assert_approx(cumulative_principal, -1000.0, 1e-8);
867    }
868
869    #[test]
870    fn rate_basic() {
871        assert_approx(eval("RATE(48,-200,8000)"), 0.007701, 0.0001);
872    }
873
874    #[test]
875    fn nper_basic() {
876        assert_approx(eval("NPER(0.01,-100,1000)"), 10.5886, 0.01);
877    }
878
879    #[test]
880    fn nper_zero_rate() {
881        assert_approx(eval("NPER(0,-100,1000)"), 10.0, 0.01);
882    }
883
884    #[test]
885    fn db_basic() {
886        assert_approx(eval("DB(1000000,100000,6,1,7)"), 186083.3333, 0.01);
887    }
888
889    #[test]
890    fn ddb_basic() {
891        assert_approx(eval("DDB(2400,300,10,1)"), 480.0, 0.01);
892    }
893
894    #[test]
895    fn ddb_later_period() {
896        assert_approx(eval("DDB(2400,300,10,2)"), 384.0, 0.01);
897    }
898
899    #[test]
900    fn sln_basic() {
901        assert_approx(eval("SLN(30000,7500,10)"), 2250.0, 0.01);
902    }
903
904    #[test]
905    fn sln_zero_life() {
906        assert_eq!(
907            eval("SLN(30000,7500,0)"),
908            CellValue::Error("#DIV/0!".to_string())
909        );
910    }
911
912    #[test]
913    fn syd_basic() {
914        assert_approx(eval("SYD(30000,7500,10,1)"), 4090.9091, 0.01);
915    }
916
917    #[test]
918    fn syd_last_period() {
919        assert_approx(eval("SYD(30000,7500,10,10)"), 409.0909, 0.01);
920    }
921
922    #[test]
923    fn effect_basic() {
924        assert_approx(eval("EFFECT(0.0525,4)"), 0.053543, 0.0001);
925    }
926
927    #[test]
928    fn effect_invalid() {
929        assert_eq!(
930            eval("EFFECT(-0.01,4)"),
931            CellValue::Error("#NUM!".to_string())
932        );
933    }
934
935    #[test]
936    fn nominal_basic() {
937        assert_approx(eval("NOMINAL(0.053543,4)"), 0.0525, 0.0001);
938    }
939
940    #[test]
941    fn dollarde_basic() {
942        assert_approx(eval("DOLLARDE(1.02,16)"), 1.125, 0.001);
943    }
944
945    #[test]
946    fn dollarfr_basic() {
947        assert_approx(eval("DOLLARFR(1.125,16)"), 1.02, 0.001);
948    }
949
950    #[test]
951    fn cumipmt_basic() {
952        assert_approx(eval("CUMIPMT(0.09/12,30*12,125000,1,1,0)"), -937.5, 0.01);
953    }
954
955    #[test]
956    fn cumprinc_basic() {
957        assert_approx(eval("CUMPRINC(0.09/12,30*12,125000,1,1,0)"), -68.2782, 0.01);
958    }
959
960    #[test]
961    fn xnpv_basic() {
962        let data = vec![
963            ("Sheet1", 1, 1, CellValue::Number(-10000.0)),
964            ("Sheet1", 1, 2, CellValue::Number(2750.0)),
965            ("Sheet1", 1, 3, CellValue::Number(4250.0)),
966            ("Sheet1", 1, 4, CellValue::Number(3250.0)),
967            ("Sheet1", 1, 5, CellValue::Number(2750.0)),
968            ("Sheet1", 2, 1, CellValue::Number(39448.0)),
969            ("Sheet1", 2, 2, CellValue::Number(39508.0)),
970            ("Sheet1", 2, 3, CellValue::Number(39600.0)),
971            ("Sheet1", 2, 4, CellValue::Number(39692.0)),
972            ("Sheet1", 2, 5, CellValue::Number(39783.0)),
973        ];
974        let result = eval_with_data("XNPV(0.09,A1:A5,B1:B5)", &data);
975        if let CellValue::Number(n) = result {
976            assert!(n > 2000.0, "XNPV should be positive, got {n}");
977        } else {
978            panic!("expected number, got {result:?}");
979        }
980    }
981
982    #[test]
983    fn xirr_basic() {
984        let data = vec![
985            ("Sheet1", 1, 1, CellValue::Number(-10000.0)),
986            ("Sheet1", 1, 2, CellValue::Number(2750.0)),
987            ("Sheet1", 1, 3, CellValue::Number(4250.0)),
988            ("Sheet1", 1, 4, CellValue::Number(3250.0)),
989            ("Sheet1", 1, 5, CellValue::Number(2750.0)),
990            ("Sheet1", 2, 1, CellValue::Number(39448.0)),
991            ("Sheet1", 2, 2, CellValue::Number(39508.0)),
992            ("Sheet1", 2, 3, CellValue::Number(39600.0)),
993            ("Sheet1", 2, 4, CellValue::Number(39692.0)),
994            ("Sheet1", 2, 5, CellValue::Number(39783.0)),
995        ];
996        let result = eval_with_data("XIRR(A1:A5,B1:B5)", &data);
997        if let CellValue::Number(n) = result {
998            assert!(n > 0.0, "XIRR should be positive, got {n}");
999        } else {
1000            panic!("expected number, got {result:?}");
1001        }
1002    }
1003}