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

sheetkit_core/formula/functions/
financial.rs
