1use 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
12fn 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
29pub 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
47pub 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
65pub 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
77pub 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
92pub 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
110pub 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
132pub 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
155pub 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
180pub 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
212pub 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
281pub 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
336pub 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
348pub 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
363pub 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
377pub 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
391pub 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
411pub 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
431pub 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
483pub 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
546pub 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
567pub 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 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}