formualizer_eval/builtins/financial/
tvm.rs

1//! Time Value of Money functions: PMT, PV, FV, NPV, NPER, RATE, IPMT, PPMT, XNPV, XIRR, DOLLARDE, DOLLARFR
2
3use crate::args::ArgSchema;
4use crate::function::Function;
5use crate::traits::{ArgumentHandle, CalcValue, FunctionContext};
6use formualizer_common::{ExcelError, LiteralValue};
7use formualizer_macros::func_caps;
8
9fn coerce_num(arg: &ArgumentHandle) -> Result<f64, ExcelError> {
10    let v = arg.value()?.into_literal();
11    coerce_literal_num(&v)
12}
13
14fn coerce_literal_num(v: &LiteralValue) -> Result<f64, ExcelError> {
15    match v {
16        LiteralValue::Number(f) => Ok(*f),
17        LiteralValue::Int(i) => Ok(*i as f64),
18        LiteralValue::Boolean(b) => Ok(if *b { 1.0 } else { 0.0 }),
19        LiteralValue::Empty => Ok(0.0),
20        LiteralValue::Error(e) => Err(e.clone()),
21        _ => Err(ExcelError::new_value()),
22    }
23}
24
25/// PMT(rate, nper, pv, [fv], [type])
26/// Calculates the payment for a loan based on constant payments and a constant interest rate
27#[derive(Debug)]
28pub struct PmtFn;
29impl Function for PmtFn {
30    func_caps!(PURE);
31    fn name(&self) -> &'static str {
32        "PMT"
33    }
34    fn min_args(&self) -> usize {
35        3
36    }
37    fn variadic(&self) -> bool {
38        true
39    }
40    fn arg_schema(&self) -> &'static [ArgSchema] {
41        use std::sync::LazyLock;
42        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
43            vec![
44                ArgSchema::number_lenient_scalar(), // rate
45                ArgSchema::number_lenient_scalar(), // nper
46                ArgSchema::number_lenient_scalar(), // pv
47                ArgSchema::number_lenient_scalar(), // fv (optional)
48                ArgSchema::number_lenient_scalar(), // type (optional)
49            ]
50        });
51        &SCHEMA[..]
52    }
53    fn eval<'a, 'b, 'c>(
54        &self,
55        args: &'c [ArgumentHandle<'a, 'b>],
56        _ctx: &dyn FunctionContext<'b>,
57    ) -> Result<CalcValue<'b>, ExcelError> {
58        let rate = coerce_num(&args[0])?;
59        let nper = coerce_num(&args[1])?;
60        let pv = coerce_num(&args[2])?;
61        let fv = if args.len() > 3 {
62            coerce_num(&args[3])?
63        } else {
64            0.0
65        };
66        let pmt_type = if args.len() > 4 {
67            coerce_num(&args[4])? as i32
68        } else {
69            0
70        };
71
72        if nper == 0.0 {
73            return Ok(CalcValue::Scalar(
74                LiteralValue::Error(ExcelError::new_num()),
75            ));
76        }
77
78        let pmt = if rate.abs() < 1e-10 {
79            // When rate is 0, PMT = -(pv + fv) / nper
80            -(pv + fv) / nper
81        } else {
82            // PMT = (rate * (pv * (1+rate)^nper + fv)) / ((1+rate)^nper - 1)
83            // With type adjustment for beginning of period
84            let factor = (1.0 + rate).powf(nper);
85            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
86            -(rate * (pv * factor + fv)) / ((factor - 1.0) * type_adj)
87        };
88
89        Ok(CalcValue::Scalar(LiteralValue::Number(pmt)))
90    }
91}
92
93/// PV(rate, nper, pmt, [fv], [type])
94/// Calculates the present value of an investment
95#[derive(Debug)]
96pub struct PvFn;
97impl Function for PvFn {
98    func_caps!(PURE);
99    fn name(&self) -> &'static str {
100        "PV"
101    }
102    fn min_args(&self) -> usize {
103        3
104    }
105    fn variadic(&self) -> bool {
106        true
107    }
108    fn arg_schema(&self) -> &'static [ArgSchema] {
109        use std::sync::LazyLock;
110        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
111            vec![
112                ArgSchema::number_lenient_scalar(),
113                ArgSchema::number_lenient_scalar(),
114                ArgSchema::number_lenient_scalar(),
115                ArgSchema::number_lenient_scalar(),
116                ArgSchema::number_lenient_scalar(),
117            ]
118        });
119        &SCHEMA[..]
120    }
121    fn eval<'a, 'b, 'c>(
122        &self,
123        args: &'c [ArgumentHandle<'a, 'b>],
124        _ctx: &dyn FunctionContext<'b>,
125    ) -> Result<CalcValue<'b>, ExcelError> {
126        let rate = coerce_num(&args[0])?;
127        let nper = coerce_num(&args[1])?;
128        let pmt = coerce_num(&args[2])?;
129        let fv = if args.len() > 3 {
130            coerce_num(&args[3])?
131        } else {
132            0.0
133        };
134        let pmt_type = if args.len() > 4 {
135            coerce_num(&args[4])? as i32
136        } else {
137            0
138        };
139
140        let pv = if rate.abs() < 1e-10 {
141            -fv - pmt * nper
142        } else {
143            let factor = (1.0 + rate).powf(nper);
144            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
145            (-fv - pmt * type_adj * (factor - 1.0) / rate) / factor
146        };
147
148        Ok(CalcValue::Scalar(LiteralValue::Number(pv)))
149    }
150}
151
152/// FV(rate, nper, pmt, [pv], [type])
153/// Calculates the future value of an investment
154#[derive(Debug)]
155pub struct FvFn;
156impl Function for FvFn {
157    func_caps!(PURE);
158    fn name(&self) -> &'static str {
159        "FV"
160    }
161    fn min_args(&self) -> usize {
162        3
163    }
164    fn variadic(&self) -> bool {
165        true
166    }
167    fn arg_schema(&self) -> &'static [ArgSchema] {
168        use std::sync::LazyLock;
169        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
170            vec![
171                ArgSchema::number_lenient_scalar(),
172                ArgSchema::number_lenient_scalar(),
173                ArgSchema::number_lenient_scalar(),
174                ArgSchema::number_lenient_scalar(),
175                ArgSchema::number_lenient_scalar(),
176            ]
177        });
178        &SCHEMA[..]
179    }
180    fn eval<'a, 'b, 'c>(
181        &self,
182        args: &'c [ArgumentHandle<'a, 'b>],
183        _ctx: &dyn FunctionContext<'b>,
184    ) -> Result<CalcValue<'b>, ExcelError> {
185        let rate = coerce_num(&args[0])?;
186        let nper = coerce_num(&args[1])?;
187        let pmt = coerce_num(&args[2])?;
188        let pv = if args.len() > 3 {
189            coerce_num(&args[3])?
190        } else {
191            0.0
192        };
193        let pmt_type = if args.len() > 4 {
194            coerce_num(&args[4])? as i32
195        } else {
196            0
197        };
198
199        let fv = if rate.abs() < 1e-10 {
200            -pv - pmt * nper
201        } else {
202            let factor = (1.0 + rate).powf(nper);
203            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
204            -pv * factor - pmt * type_adj * (factor - 1.0) / rate
205        };
206
207        Ok(CalcValue::Scalar(LiteralValue::Number(fv)))
208    }
209}
210
211/// NPV(rate, value1, [value2], ...)
212/// Calculates the net present value of an investment
213#[derive(Debug)]
214pub struct NpvFn;
215impl Function for NpvFn {
216    func_caps!(PURE);
217    fn name(&self) -> &'static str {
218        "NPV"
219    }
220    fn min_args(&self) -> usize {
221        2
222    }
223    fn variadic(&self) -> bool {
224        true
225    }
226    fn arg_schema(&self) -> &'static [ArgSchema] {
227        use std::sync::LazyLock;
228        static SCHEMA: LazyLock<Vec<ArgSchema>> =
229            LazyLock::new(|| vec![ArgSchema::number_lenient_scalar(), ArgSchema::any()]);
230        &SCHEMA[..]
231    }
232    fn eval<'a, 'b, 'c>(
233        &self,
234        args: &'c [ArgumentHandle<'a, 'b>],
235        _ctx: &dyn FunctionContext<'b>,
236    ) -> Result<CalcValue<'b>, ExcelError> {
237        let rate = coerce_num(&args[0])?;
238
239        let mut npv = 0.0;
240        let mut period = 1;
241
242        for arg in &args[1..] {
243            let v = arg.value()?.into_literal();
244            match v {
245                LiteralValue::Number(n) => {
246                    npv += n / (1.0 + rate).powi(period);
247                    period += 1;
248                }
249                LiteralValue::Int(i) => {
250                    npv += (i as f64) / (1.0 + rate).powi(period);
251                    period += 1;
252                }
253                LiteralValue::Error(e) => {
254                    return Ok(CalcValue::Scalar(LiteralValue::Error(e)));
255                }
256                LiteralValue::Array(arr) => {
257                    for row in arr {
258                        for cell in row {
259                            match cell {
260                                LiteralValue::Number(n) => {
261                                    npv += n / (1.0 + rate).powi(period);
262                                    period += 1;
263                                }
264                                LiteralValue::Int(i) => {
265                                    npv += (i as f64) / (1.0 + rate).powi(period);
266                                    period += 1;
267                                }
268                                LiteralValue::Error(e) => {
269                                    return Ok(CalcValue::Scalar(LiteralValue::Error(e)));
270                                }
271                                _ => {} // Skip non-numeric values
272                            }
273                        }
274                    }
275                }
276                _ => {} // Skip non-numeric values
277            }
278        }
279
280        Ok(CalcValue::Scalar(LiteralValue::Number(npv)))
281    }
282}
283
284/// NPER(rate, pmt, pv, [fv], [type])
285/// Calculates the number of periods for an investment
286#[derive(Debug)]
287pub struct NperFn;
288impl Function for NperFn {
289    func_caps!(PURE);
290    fn name(&self) -> &'static str {
291        "NPER"
292    }
293    fn min_args(&self) -> usize {
294        3
295    }
296    fn variadic(&self) -> bool {
297        true
298    }
299    fn arg_schema(&self) -> &'static [ArgSchema] {
300        use std::sync::LazyLock;
301        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
302            vec![
303                ArgSchema::number_lenient_scalar(),
304                ArgSchema::number_lenient_scalar(),
305                ArgSchema::number_lenient_scalar(),
306                ArgSchema::number_lenient_scalar(),
307                ArgSchema::number_lenient_scalar(),
308            ]
309        });
310        &SCHEMA[..]
311    }
312    fn eval<'a, 'b, 'c>(
313        &self,
314        args: &'c [ArgumentHandle<'a, 'b>],
315        _ctx: &dyn FunctionContext<'b>,
316    ) -> Result<CalcValue<'b>, ExcelError> {
317        let rate = coerce_num(&args[0])?;
318        let pmt = coerce_num(&args[1])?;
319        let pv = coerce_num(&args[2])?;
320        let fv = if args.len() > 3 {
321            coerce_num(&args[3])?
322        } else {
323            0.0
324        };
325        let pmt_type = if args.len() > 4 {
326            coerce_num(&args[4])? as i32
327        } else {
328            0
329        };
330
331        let nper = if rate.abs() < 1e-10 {
332            if pmt.abs() < 1e-10 {
333                return Ok(CalcValue::Scalar(
334                    LiteralValue::Error(ExcelError::new_num()),
335                ));
336            }
337            -(pv + fv) / pmt
338        } else {
339            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
340            let pmt_adj = pmt * type_adj;
341            let numerator = pmt_adj - fv * rate;
342            let denominator = pv * rate + pmt_adj;
343            if numerator / denominator <= 0.0 {
344                return Ok(CalcValue::Scalar(
345                    LiteralValue::Error(ExcelError::new_num()),
346                ));
347            }
348            (numerator / denominator).ln() / (1.0 + rate).ln()
349        };
350
351        Ok(CalcValue::Scalar(LiteralValue::Number(nper)))
352    }
353}
354
355/// RATE(nper, pmt, pv, [fv], [type], [guess])
356/// Calculates the interest rate per period
357#[derive(Debug)]
358pub struct RateFn;
359impl Function for RateFn {
360    func_caps!(PURE);
361    fn name(&self) -> &'static str {
362        "RATE"
363    }
364    fn min_args(&self) -> usize {
365        3
366    }
367    fn variadic(&self) -> bool {
368        true
369    }
370    fn arg_schema(&self) -> &'static [ArgSchema] {
371        use std::sync::LazyLock;
372        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
373            vec![
374                ArgSchema::number_lenient_scalar(),
375                ArgSchema::number_lenient_scalar(),
376                ArgSchema::number_lenient_scalar(),
377                ArgSchema::number_lenient_scalar(),
378                ArgSchema::number_lenient_scalar(),
379                ArgSchema::number_lenient_scalar(),
380            ]
381        });
382        &SCHEMA[..]
383    }
384    fn eval<'a, 'b, 'c>(
385        &self,
386        args: &'c [ArgumentHandle<'a, 'b>],
387        _ctx: &dyn FunctionContext<'b>,
388    ) -> Result<CalcValue<'b>, ExcelError> {
389        let nper = coerce_num(&args[0])?;
390        let pmt = coerce_num(&args[1])?;
391        let pv = coerce_num(&args[2])?;
392        let fv = if args.len() > 3 {
393            coerce_num(&args[3])?
394        } else {
395            0.0
396        };
397        let pmt_type = if args.len() > 4 {
398            coerce_num(&args[4])? as i32
399        } else {
400            0
401        };
402        let guess = if args.len() > 5 {
403            coerce_num(&args[5])?
404        } else {
405            0.1
406        };
407
408        // Newton-Raphson iteration to find rate
409        let mut rate = guess;
410        let max_iter = 100;
411        let tolerance = 1e-10;
412
413        for _ in 0..max_iter {
414            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
415
416            if rate.abs() < 1e-10 {
417                // Special case for very small rate
418                let f = pv + pmt * nper + fv;
419                if f.abs() < tolerance {
420                    return Ok(CalcValue::Scalar(LiteralValue::Number(rate)));
421                }
422                rate = 0.01; // Nudge away from zero
423                continue;
424            }
425
426            let factor = (1.0 + rate).powf(nper);
427            let f = pv * factor + pmt * type_adj * (factor - 1.0) / rate + fv;
428
429            // Derivative
430            let factor_prime = nper * (1.0 + rate).powf(nper - 1.0);
431            let df = pv * factor_prime
432                + pmt * type_adj * (factor_prime / rate - (factor - 1.0) / (rate * rate));
433
434            if df.abs() < 1e-20 {
435                break;
436            }
437
438            let new_rate = rate - f / df;
439
440            if (new_rate - rate).abs() < tolerance {
441                return Ok(CalcValue::Scalar(LiteralValue::Number(new_rate)));
442            }
443
444            rate = new_rate;
445
446            // Prevent rate from going too negative
447            if rate < -0.99 {
448                rate = -0.99;
449            }
450        }
451
452        // If we didn't converge, return error
453        Ok(CalcValue::Scalar(
454            LiteralValue::Error(ExcelError::new_num()),
455        ))
456    }
457}
458
459/// IPMT(rate, per, nper, pv, [fv], [type])
460/// Calculates the interest payment for a given period
461#[derive(Debug)]
462pub struct IpmtFn;
463impl Function for IpmtFn {
464    func_caps!(PURE);
465    fn name(&self) -> &'static str {
466        "IPMT"
467    }
468    fn min_args(&self) -> usize {
469        4
470    }
471    fn variadic(&self) -> bool {
472        true
473    }
474    fn arg_schema(&self) -> &'static [ArgSchema] {
475        use std::sync::LazyLock;
476        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
477            vec![
478                ArgSchema::number_lenient_scalar(),
479                ArgSchema::number_lenient_scalar(),
480                ArgSchema::number_lenient_scalar(),
481                ArgSchema::number_lenient_scalar(),
482                ArgSchema::number_lenient_scalar(),
483                ArgSchema::number_lenient_scalar(),
484            ]
485        });
486        &SCHEMA[..]
487    }
488    fn eval<'a, 'b, 'c>(
489        &self,
490        args: &'c [ArgumentHandle<'a, 'b>],
491        _ctx: &dyn FunctionContext<'b>,
492    ) -> Result<CalcValue<'b>, ExcelError> {
493        let rate = coerce_num(&args[0])?;
494        let per = coerce_num(&args[1])?;
495        let nper = coerce_num(&args[2])?;
496        let pv = coerce_num(&args[3])?;
497        let fv = if args.len() > 4 {
498            coerce_num(&args[4])?
499        } else {
500            0.0
501        };
502        let pmt_type = if args.len() > 5 {
503            coerce_num(&args[5])? as i32
504        } else {
505            0
506        };
507
508        if per < 1.0 || per > nper {
509            return Ok(CalcValue::Scalar(
510                LiteralValue::Error(ExcelError::new_num()),
511            ));
512        }
513
514        // Calculate PMT first
515        let pmt = if rate.abs() < 1e-10 {
516            -(pv + fv) / nper
517        } else {
518            let factor = (1.0 + rate).powf(nper);
519            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
520            -(rate * (pv * factor + fv)) / ((factor - 1.0) * type_adj)
521        };
522
523        // Calculate FV at start of period
524        let fv_at_start = if rate.abs() < 1e-10 {
525            -pv - pmt * (per - 1.0)
526        } else {
527            let factor = (1.0 + rate).powf(per - 1.0);
528            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
529            -pv * factor - pmt * type_adj * (factor - 1.0) / rate
530        };
531
532        // Interest is rate * balance at start of period
533        // fv_at_start is negative of balance, so ipmt = fv_at_start * rate
534        let ipmt = if pmt_type != 0 && per == 1.0 {
535            0.0 // No interest in first period for annuity due
536        } else {
537            fv_at_start * rate
538        };
539
540        Ok(CalcValue::Scalar(LiteralValue::Number(ipmt)))
541    }
542}
543
544/// PPMT(rate, per, nper, pv, [fv], [type])
545/// Calculates the principal payment for a given period
546#[derive(Debug)]
547pub struct PpmtFn;
548impl Function for PpmtFn {
549    func_caps!(PURE);
550    fn name(&self) -> &'static str {
551        "PPMT"
552    }
553    fn min_args(&self) -> usize {
554        4
555    }
556    fn variadic(&self) -> bool {
557        true
558    }
559    fn arg_schema(&self) -> &'static [ArgSchema] {
560        use std::sync::LazyLock;
561        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
562            vec![
563                ArgSchema::number_lenient_scalar(),
564                ArgSchema::number_lenient_scalar(),
565                ArgSchema::number_lenient_scalar(),
566                ArgSchema::number_lenient_scalar(),
567                ArgSchema::number_lenient_scalar(),
568                ArgSchema::number_lenient_scalar(),
569            ]
570        });
571        &SCHEMA[..]
572    }
573    fn eval<'a, 'b, 'c>(
574        &self,
575        args: &'c [ArgumentHandle<'a, 'b>],
576        _ctx: &dyn FunctionContext<'b>,
577    ) -> Result<CalcValue<'b>, ExcelError> {
578        let rate = coerce_num(&args[0])?;
579        let per = coerce_num(&args[1])?;
580        let nper = coerce_num(&args[2])?;
581        let pv = coerce_num(&args[3])?;
582        let fv = if args.len() > 4 {
583            coerce_num(&args[4])?
584        } else {
585            0.0
586        };
587        let pmt_type = if args.len() > 5 {
588            coerce_num(&args[5])? as i32
589        } else {
590            0
591        };
592
593        if per < 1.0 || per > nper {
594            return Ok(CalcValue::Scalar(
595                LiteralValue::Error(ExcelError::new_num()),
596            ));
597        }
598
599        // Calculate PMT
600        let pmt = if rate.abs() < 1e-10 {
601            -(pv + fv) / nper
602        } else {
603            let factor = (1.0 + rate).powf(nper);
604            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
605            -(rate * (pv * factor + fv)) / ((factor - 1.0) * type_adj)
606        };
607
608        // Calculate IPMT
609        let fv_at_start = if rate.abs() < 1e-10 {
610            -pv - pmt * (per - 1.0)
611        } else {
612            let factor = (1.0 + rate).powf(per - 1.0);
613            let type_adj = if pmt_type != 0 { 1.0 + rate } else { 1.0 };
614            -pv * factor - pmt * type_adj * (factor - 1.0) / rate
615        };
616
617        let ipmt = if pmt_type != 0 && per == 1.0 {
618            0.0
619        } else {
620            fv_at_start * rate
621        };
622
623        // PPMT = PMT - IPMT
624        let ppmt = pmt - ipmt;
625
626        Ok(CalcValue::Scalar(LiteralValue::Number(ppmt)))
627    }
628}
629
630/// EFFECT(nominal_rate, npery) - Returns the effective annual interest rate
631#[derive(Debug)]
632pub struct EffectFn;
633impl Function for EffectFn {
634    func_caps!(PURE);
635    fn name(&self) -> &'static str {
636        "EFFECT"
637    }
638    fn min_args(&self) -> usize {
639        2
640    }
641    fn arg_schema(&self) -> &'static [ArgSchema] {
642        use std::sync::LazyLock;
643        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
644            vec![
645                ArgSchema::number_lenient_scalar(),
646                ArgSchema::number_lenient_scalar(),
647            ]
648        });
649        &SCHEMA[..]
650    }
651    fn eval<'a, 'b, 'c>(
652        &self,
653        args: &'c [ArgumentHandle<'a, 'b>],
654        _ctx: &dyn FunctionContext<'b>,
655    ) -> Result<CalcValue<'b>, ExcelError> {
656        let nominal_rate = coerce_num(&args[0])?;
657        let npery = coerce_num(&args[1])?.trunc() as i32;
658
659        // Validation
660        if nominal_rate <= 0.0 || npery < 1 {
661            return Ok(CalcValue::Scalar(
662                LiteralValue::Error(ExcelError::new_num()),
663            ));
664        }
665
666        // EFFECT = (1 + nominal_rate/npery)^npery - 1
667        let effect = (1.0 + nominal_rate / npery as f64).powi(npery) - 1.0;
668        Ok(CalcValue::Scalar(LiteralValue::Number(effect)))
669    }
670}
671
672/// NOMINAL(effect_rate, npery) - Returns the nominal annual interest rate
673#[derive(Debug)]
674pub struct NominalFn;
675impl Function for NominalFn {
676    func_caps!(PURE);
677    fn name(&self) -> &'static str {
678        "NOMINAL"
679    }
680    fn min_args(&self) -> usize {
681        2
682    }
683    fn arg_schema(&self) -> &'static [ArgSchema] {
684        use std::sync::LazyLock;
685        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
686            vec![
687                ArgSchema::number_lenient_scalar(),
688                ArgSchema::number_lenient_scalar(),
689            ]
690        });
691        &SCHEMA[..]
692    }
693    fn eval<'a, 'b, 'c>(
694        &self,
695        args: &'c [ArgumentHandle<'a, 'b>],
696        _ctx: &dyn FunctionContext<'b>,
697    ) -> Result<CalcValue<'b>, ExcelError> {
698        let effect_rate = coerce_num(&args[0])?;
699        let npery = coerce_num(&args[1])?.trunc() as i32;
700
701        // Validation
702        if effect_rate <= 0.0 || npery < 1 {
703            return Ok(CalcValue::Scalar(
704                LiteralValue::Error(ExcelError::new_num()),
705            ));
706        }
707
708        // NOMINAL = npery * ((1 + effect_rate)^(1/npery) - 1)
709        let nominal = npery as f64 * ((1.0 + effect_rate).powf(1.0 / npery as f64) - 1.0);
710        Ok(CalcValue::Scalar(LiteralValue::Number(nominal)))
711    }
712}
713
714/// IRR(values, [guess]) - Internal rate of return
715#[derive(Debug)]
716pub struct IrrFn;
717impl Function for IrrFn {
718    func_caps!(PURE);
719    fn name(&self) -> &'static str {
720        "IRR"
721    }
722    fn min_args(&self) -> usize {
723        1
724    }
725    fn variadic(&self) -> bool {
726        true
727    }
728    fn arg_schema(&self) -> &'static [ArgSchema] {
729        use std::sync::LazyLock;
730        static SCHEMA: LazyLock<Vec<ArgSchema>> =
731            LazyLock::new(|| vec![ArgSchema::any(), ArgSchema::number_lenient_scalar()]);
732        &SCHEMA[..]
733    }
734    fn eval<'a, 'b, 'c>(
735        &self,
736        args: &'c [ArgumentHandle<'a, 'b>],
737        _ctx: &dyn FunctionContext<'b>,
738    ) -> Result<CalcValue<'b>, ExcelError> {
739        // Collect cash flows
740        let mut cashflows = Vec::new();
741        let val = args[0].value()?;
742        match val {
743            CalcValue::Scalar(lit) => match lit {
744                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
745                LiteralValue::Array(arr) => {
746                    for row in arr {
747                        for cell in row {
748                            if let Ok(n) = coerce_literal_num(&cell) {
749                                cashflows.push(n);
750                            }
751                        }
752                    }
753                }
754                other => cashflows.push(coerce_literal_num(&other)?),
755            },
756            CalcValue::Range(range) => {
757                let (rows, cols) = range.dims();
758                for r in 0..rows {
759                    for c in 0..cols {
760                        let cell = range.get_cell(r, c);
761                        if let Ok(n) = coerce_literal_num(&cell) {
762                            cashflows.push(n);
763                        }
764                    }
765                }
766            }
767        }
768
769        if cashflows.len() < 2 {
770            return Ok(CalcValue::Scalar(
771                LiteralValue::Error(ExcelError::new_num()),
772            ));
773        }
774
775        // Initial guess
776        let guess = if args.len() > 1 {
777            coerce_num(&args[1])?
778        } else {
779            0.1
780        };
781
782        // Newton-Raphson iteration to find IRR
783        let mut rate = guess;
784        const MAX_ITER: i32 = 100;
785        const EPSILON: f64 = 1e-10;
786
787        for _ in 0..MAX_ITER {
788            let mut npv = 0.0;
789            let mut d_npv = 0.0;
790
791            for (i, &cf) in cashflows.iter().enumerate() {
792                let factor = (1.0 + rate).powi(i as i32);
793                npv += cf / factor;
794                if i > 0 {
795                    d_npv -= (i as f64) * cf / (factor * (1.0 + rate));
796                }
797            }
798
799            if d_npv.abs() < EPSILON {
800                return Ok(CalcValue::Scalar(
801                    LiteralValue::Error(ExcelError::new_num()),
802                ));
803            }
804
805            let new_rate = rate - npv / d_npv;
806            if (new_rate - rate).abs() < EPSILON {
807                return Ok(CalcValue::Scalar(LiteralValue::Number(new_rate)));
808            }
809            rate = new_rate;
810        }
811
812        Ok(CalcValue::Scalar(
813            LiteralValue::Error(ExcelError::new_num()),
814        ))
815    }
816}
817
818/// MIRR(values, finance_rate, reinvest_rate) - Modified IRR
819#[derive(Debug)]
820pub struct MirrFn;
821impl Function for MirrFn {
822    func_caps!(PURE);
823    fn name(&self) -> &'static str {
824        "MIRR"
825    }
826    fn min_args(&self) -> usize {
827        3
828    }
829    fn arg_schema(&self) -> &'static [ArgSchema] {
830        use std::sync::LazyLock;
831        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
832            vec![
833                ArgSchema::any(),
834                ArgSchema::number_lenient_scalar(),
835                ArgSchema::number_lenient_scalar(),
836            ]
837        });
838        &SCHEMA[..]
839    }
840    fn eval<'a, 'b, 'c>(
841        &self,
842        args: &'c [ArgumentHandle<'a, 'b>],
843        _ctx: &dyn FunctionContext<'b>,
844    ) -> Result<CalcValue<'b>, ExcelError> {
845        // Collect cash flows
846        let mut cashflows = Vec::new();
847        let val = args[0].value()?;
848        match val {
849            CalcValue::Scalar(lit) => match lit {
850                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
851                LiteralValue::Array(arr) => {
852                    for row in arr {
853                        for cell in row {
854                            if let Ok(n) = coerce_literal_num(&cell) {
855                                cashflows.push(n);
856                            }
857                        }
858                    }
859                }
860                other => cashflows.push(coerce_literal_num(&other)?),
861            },
862            CalcValue::Range(range) => {
863                let (rows, cols) = range.dims();
864                for r in 0..rows {
865                    for c in 0..cols {
866                        let cell = range.get_cell(r, c);
867                        if let Ok(n) = coerce_literal_num(&cell) {
868                            cashflows.push(n);
869                        }
870                    }
871                }
872            }
873        }
874
875        let finance_rate = coerce_num(&args[1])?;
876        let reinvest_rate = coerce_num(&args[2])?;
877
878        if cashflows.len() < 2 {
879            return Ok(CalcValue::Scalar(
880                LiteralValue::Error(ExcelError::new_num()),
881            ));
882        }
883
884        let n = cashflows.len() as i32;
885
886        // Present value of negative cash flows (discounted at finance_rate)
887        let mut pv_neg = 0.0;
888        // Future value of positive cash flows (compounded at reinvest_rate)
889        let mut fv_pos = 0.0;
890
891        for (i, &cf) in cashflows.iter().enumerate() {
892            if cf < 0.0 {
893                pv_neg += cf / (1.0 + finance_rate).powi(i as i32);
894            } else {
895                fv_pos += cf * (1.0 + reinvest_rate).powi(n - 1 - i as i32);
896            }
897        }
898
899        if pv_neg >= 0.0 || fv_pos <= 0.0 {
900            return Ok(CalcValue::Scalar(
901                LiteralValue::Error(ExcelError::new_div()),
902            ));
903        }
904
905        // MIRR = (FV_pos / -PV_neg)^(1/(n-1)) - 1
906        let mirr = (-fv_pos / pv_neg).powf(1.0 / (n - 1) as f64) - 1.0;
907        Ok(CalcValue::Scalar(LiteralValue::Number(mirr)))
908    }
909}
910
911/// CUMIPMT(rate, nper, pv, start_period, end_period, type) - Cumulative interest payment
912#[derive(Debug)]
913pub struct CumipmtFn;
914impl Function for CumipmtFn {
915    func_caps!(PURE);
916    fn name(&self) -> &'static str {
917        "CUMIPMT"
918    }
919    fn min_args(&self) -> usize {
920        6
921    }
922    fn arg_schema(&self) -> &'static [ArgSchema] {
923        use std::sync::LazyLock;
924        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
925            vec![
926                ArgSchema::number_lenient_scalar(),
927                ArgSchema::number_lenient_scalar(),
928                ArgSchema::number_lenient_scalar(),
929                ArgSchema::number_lenient_scalar(),
930                ArgSchema::number_lenient_scalar(),
931                ArgSchema::number_lenient_scalar(),
932            ]
933        });
934        &SCHEMA[..]
935    }
936    fn eval<'a, 'b, 'c>(
937        &self,
938        args: &'c [ArgumentHandle<'a, 'b>],
939        _ctx: &dyn FunctionContext<'b>,
940    ) -> Result<CalcValue<'b>, ExcelError> {
941        let rate = coerce_num(&args[0])?;
942        let nper = coerce_num(&args[1])?.trunc() as i32;
943        let pv = coerce_num(&args[2])?;
944        let start = coerce_num(&args[3])?.trunc() as i32;
945        let end = coerce_num(&args[4])?.trunc() as i32;
946        let pay_type = coerce_num(&args[5])?.trunc() as i32;
947
948        // Validation
949        if rate <= 0.0
950            || nper <= 0
951            || pv <= 0.0
952            || start < 1
953            || end < start
954            || end > nper
955            || (pay_type != 0 && pay_type != 1)
956        {
957            return Ok(CalcValue::Scalar(
958                LiteralValue::Error(ExcelError::new_num()),
959            ));
960        }
961
962        // Calculate PMT
963        let pmt = if rate == 0.0 {
964            -pv / nper as f64
965        } else {
966            -pv * rate * (1.0 + rate).powi(nper) / ((1.0 + rate).powi(nper) - 1.0)
967        };
968
969        // Sum interest payments from start to end
970        let mut cum_int = 0.0;
971        let mut balance = pv;
972
973        for period in 1..=end {
974            let interest = if pay_type == 1 && period == 1 {
975                0.0
976            } else {
977                balance * rate
978            };
979
980            if period >= start {
981                cum_int += interest;
982            }
983
984            let principal = pmt - interest;
985            balance += principal;
986        }
987
988        Ok(CalcValue::Scalar(LiteralValue::Number(cum_int)))
989    }
990}
991
992/// CUMPRINC(rate, nper, pv, start_period, end_period, type) - Cumulative principal payment
993#[derive(Debug)]
994pub struct CumprincFn;
995impl Function for CumprincFn {
996    func_caps!(PURE);
997    fn name(&self) -> &'static str {
998        "CUMPRINC"
999    }
1000    fn min_args(&self) -> usize {
1001        6
1002    }
1003    fn arg_schema(&self) -> &'static [ArgSchema] {
1004        use std::sync::LazyLock;
1005        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
1006            vec![
1007                ArgSchema::number_lenient_scalar(),
1008                ArgSchema::number_lenient_scalar(),
1009                ArgSchema::number_lenient_scalar(),
1010                ArgSchema::number_lenient_scalar(),
1011                ArgSchema::number_lenient_scalar(),
1012                ArgSchema::number_lenient_scalar(),
1013            ]
1014        });
1015        &SCHEMA[..]
1016    }
1017    fn eval<'a, 'b, 'c>(
1018        &self,
1019        args: &'c [ArgumentHandle<'a, 'b>],
1020        _ctx: &dyn FunctionContext<'b>,
1021    ) -> Result<CalcValue<'b>, ExcelError> {
1022        let rate = coerce_num(&args[0])?;
1023        let nper = coerce_num(&args[1])?.trunc() as i32;
1024        let pv = coerce_num(&args[2])?;
1025        let start = coerce_num(&args[3])?.trunc() as i32;
1026        let end = coerce_num(&args[4])?.trunc() as i32;
1027        let pay_type = coerce_num(&args[5])?.trunc() as i32;
1028
1029        // Validation
1030        if rate <= 0.0
1031            || nper <= 0
1032            || pv <= 0.0
1033            || start < 1
1034            || end < start
1035            || end > nper
1036            || (pay_type != 0 && pay_type != 1)
1037        {
1038            return Ok(CalcValue::Scalar(
1039                LiteralValue::Error(ExcelError::new_num()),
1040            ));
1041        }
1042
1043        // Calculate PMT
1044        let pmt = if rate == 0.0 {
1045            -pv / nper as f64
1046        } else {
1047            -pv * rate * (1.0 + rate).powi(nper) / ((1.0 + rate).powi(nper) - 1.0)
1048        };
1049
1050        // Sum principal payments from start to end
1051        let mut cum_princ = 0.0;
1052        let mut balance = pv;
1053
1054        for period in 1..=end {
1055            let interest = if pay_type == 1 && period == 1 {
1056                0.0
1057            } else {
1058                balance * rate
1059            };
1060
1061            let principal = pmt - interest;
1062
1063            if period >= start {
1064                cum_princ += principal;
1065            }
1066
1067            balance += principal;
1068        }
1069
1070        Ok(CalcValue::Scalar(LiteralValue::Number(cum_princ)))
1071    }
1072}
1073
1074/// XNPV(rate, values, dates) - Net present value for irregular cash flows
1075/// Formula: Sum of values[i] / (1 + rate)^((dates[i] - dates[0]) / 365)
1076#[derive(Debug)]
1077pub struct XnpvFn;
1078impl Function for XnpvFn {
1079    func_caps!(PURE);
1080    fn name(&self) -> &'static str {
1081        "XNPV"
1082    }
1083    fn min_args(&self) -> usize {
1084        3
1085    }
1086    fn arg_schema(&self) -> &'static [ArgSchema] {
1087        use std::sync::LazyLock;
1088        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
1089            vec![
1090                ArgSchema::number_lenient_scalar(), // rate
1091                ArgSchema::any(),                   // values
1092                ArgSchema::any(),                   // dates
1093            ]
1094        });
1095        &SCHEMA[..]
1096    }
1097    fn eval<'a, 'b, 'c>(
1098        &self,
1099        args: &'c [ArgumentHandle<'a, 'b>],
1100        _ctx: &dyn FunctionContext<'b>,
1101    ) -> Result<CalcValue<'b>, ExcelError> {
1102        let rate = coerce_num(&args[0])?;
1103
1104        // Collect values
1105        let mut values = Vec::new();
1106        let val = args[1].value()?;
1107        match val {
1108            CalcValue::Scalar(lit) => match lit {
1109                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
1110                LiteralValue::Array(arr) => {
1111                    for row in arr {
1112                        for cell in row {
1113                            if let Ok(n) = coerce_literal_num(&cell) {
1114                                values.push(n);
1115                            }
1116                        }
1117                    }
1118                }
1119                other => values.push(coerce_literal_num(&other)?),
1120            },
1121            CalcValue::Range(range) => {
1122                let (rows, cols) = range.dims();
1123                for r in 0..rows {
1124                    for c in 0..cols {
1125                        let cell = range.get_cell(r, c);
1126                        if let Ok(n) = coerce_literal_num(&cell) {
1127                            values.push(n);
1128                        }
1129                    }
1130                }
1131            }
1132        }
1133
1134        // Collect dates
1135        let mut dates = Vec::new();
1136        let date_val = args[2].value()?;
1137        match date_val {
1138            CalcValue::Scalar(lit) => match lit {
1139                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
1140                LiteralValue::Array(arr) => {
1141                    for row in arr {
1142                        for cell in row {
1143                            if let Ok(n) = coerce_literal_num(&cell) {
1144                                dates.push(n);
1145                            }
1146                        }
1147                    }
1148                }
1149                other => dates.push(coerce_literal_num(&other)?),
1150            },
1151            CalcValue::Range(range) => {
1152                let (rows, cols) = range.dims();
1153                for r in 0..rows {
1154                    for c in 0..cols {
1155                        let cell = range.get_cell(r, c);
1156                        if let Ok(n) = coerce_literal_num(&cell) {
1157                            dates.push(n);
1158                        }
1159                    }
1160                }
1161            }
1162        }
1163
1164        // Validate that values and dates have the same length
1165        if values.len() != dates.len() || values.is_empty() {
1166            return Ok(CalcValue::Scalar(
1167                LiteralValue::Error(ExcelError::new_num()),
1168            ));
1169        }
1170
1171        // Calculate XNPV: Sum of values[i] / (1 + rate)^((dates[i] - dates[0]) / 365)
1172        let first_date = dates[0];
1173        let mut xnpv = 0.0;
1174
1175        for (i, &value) in values.iter().enumerate() {
1176            let days_from_start = dates[i] - first_date;
1177            let years = days_from_start / 365.0;
1178            xnpv += value / (1.0 + rate).powf(years);
1179        }
1180
1181        Ok(CalcValue::Scalar(LiteralValue::Number(xnpv)))
1182    }
1183}
1184
1185/// Helper function to calculate XNPV given rate, values, and dates
1186fn calculate_xnpv(rate: f64, values: &[f64], dates: &[f64]) -> f64 {
1187    if values.is_empty() || dates.is_empty() {
1188        return 0.0;
1189    }
1190    let first_date = dates[0];
1191    let mut xnpv = 0.0;
1192    for (i, &value) in values.iter().enumerate() {
1193        let days_from_start = dates[i] - first_date;
1194        let years = days_from_start / 365.0;
1195        xnpv += value / (1.0 + rate).powf(years);
1196    }
1197    xnpv
1198}
1199
1200/// Helper function to calculate the derivative of XNPV with respect to rate
1201fn calculate_xnpv_derivative(rate: f64, values: &[f64], dates: &[f64]) -> f64 {
1202    if values.is_empty() || dates.is_empty() {
1203        return 0.0;
1204    }
1205    let first_date = dates[0];
1206    let mut d_xnpv = 0.0;
1207    for (i, &value) in values.iter().enumerate() {
1208        let days_from_start = dates[i] - first_date;
1209        let years = days_from_start / 365.0;
1210        // d/dr [value / (1+r)^years] = -years * value / (1+r)^(years+1)
1211        d_xnpv -= years * value / (1.0 + rate).powf(years + 1.0);
1212    }
1213    d_xnpv
1214}
1215
1216/// XIRR(values, dates, [guess]) - Internal rate of return for irregular cash flows
1217/// Uses Newton-Raphson iteration to find rate where XNPV = 0
1218#[derive(Debug)]
1219pub struct XirrFn;
1220impl Function for XirrFn {
1221    func_caps!(PURE);
1222    fn name(&self) -> &'static str {
1223        "XIRR"
1224    }
1225    fn min_args(&self) -> usize {
1226        2
1227    }
1228    fn variadic(&self) -> bool {
1229        true
1230    }
1231    fn arg_schema(&self) -> &'static [ArgSchema] {
1232        use std::sync::LazyLock;
1233        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
1234            vec![
1235                ArgSchema::any(),                   // values
1236                ArgSchema::any(),                   // dates
1237                ArgSchema::number_lenient_scalar(), // guess (optional)
1238            ]
1239        });
1240        &SCHEMA[..]
1241    }
1242    fn eval<'a, 'b, 'c>(
1243        &self,
1244        args: &'c [ArgumentHandle<'a, 'b>],
1245        _ctx: &dyn FunctionContext<'b>,
1246    ) -> Result<CalcValue<'b>, ExcelError> {
1247        // Collect values
1248        let mut values = Vec::new();
1249        let val = args[0].value()?;
1250        match val {
1251            CalcValue::Scalar(lit) => match lit {
1252                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
1253                LiteralValue::Array(arr) => {
1254                    for row in arr {
1255                        for cell in row {
1256                            if let Ok(n) = coerce_literal_num(&cell) {
1257                                values.push(n);
1258                            }
1259                        }
1260                    }
1261                }
1262                other => values.push(coerce_literal_num(&other)?),
1263            },
1264            CalcValue::Range(range) => {
1265                let (rows, cols) = range.dims();
1266                for r in 0..rows {
1267                    for c in 0..cols {
1268                        let cell = range.get_cell(r, c);
1269                        if let Ok(n) = coerce_literal_num(&cell) {
1270                            values.push(n);
1271                        }
1272                    }
1273                }
1274            }
1275        }
1276
1277        // Collect dates
1278        let mut dates = Vec::new();
1279        let date_val = args[1].value()?;
1280        match date_val {
1281            CalcValue::Scalar(lit) => match lit {
1282                LiteralValue::Error(e) => return Ok(CalcValue::Scalar(LiteralValue::Error(e))),
1283                LiteralValue::Array(arr) => {
1284                    for row in arr {
1285                        for cell in row {
1286                            if let Ok(n) = coerce_literal_num(&cell) {
1287                                dates.push(n);
1288                            }
1289                        }
1290                    }
1291                }
1292                other => dates.push(coerce_literal_num(&other)?),
1293            },
1294            CalcValue::Range(range) => {
1295                let (rows, cols) = range.dims();
1296                for r in 0..rows {
1297                    for c in 0..cols {
1298                        let cell = range.get_cell(r, c);
1299                        if let Ok(n) = coerce_literal_num(&cell) {
1300                            dates.push(n);
1301                        }
1302                    }
1303                }
1304            }
1305        }
1306
1307        // Validate
1308        if values.len() != dates.len() || values.len() < 2 {
1309            return Ok(CalcValue::Scalar(
1310                LiteralValue::Error(ExcelError::new_num()),
1311            ));
1312        }
1313
1314        // Check that we have at least one positive and one negative cash flow
1315        let has_positive = values.iter().any(|&v| v > 0.0);
1316        let has_negative = values.iter().any(|&v| v < 0.0);
1317        if !has_positive || !has_negative {
1318            return Ok(CalcValue::Scalar(
1319                LiteralValue::Error(ExcelError::new_num()),
1320            ));
1321        }
1322
1323        // Initial guess
1324        let guess = if args.len() > 2 {
1325            coerce_num(&args[2])?
1326        } else {
1327            0.1
1328        };
1329
1330        // Newton-Raphson iteration to find XIRR
1331        let mut rate = guess;
1332        const MAX_ITER: i32 = 100;
1333        const EPSILON: f64 = 1e-10;
1334
1335        for _ in 0..MAX_ITER {
1336            let xnpv = calculate_xnpv(rate, &values, &dates);
1337            let d_xnpv = calculate_xnpv_derivative(rate, &values, &dates);
1338
1339            if d_xnpv.abs() < EPSILON {
1340                return Ok(CalcValue::Scalar(
1341                    LiteralValue::Error(ExcelError::new_num()),
1342                ));
1343            }
1344
1345            let new_rate = rate - xnpv / d_xnpv;
1346
1347            if (new_rate - rate).abs() < EPSILON {
1348                return Ok(CalcValue::Scalar(LiteralValue::Number(new_rate)));
1349            }
1350
1351            rate = new_rate;
1352
1353            // Prevent rate from going too negative (would make (1+rate) negative)
1354            if rate <= -1.0 {
1355                rate = -0.99;
1356            }
1357        }
1358
1359        Ok(CalcValue::Scalar(
1360            LiteralValue::Error(ExcelError::new_num()),
1361        ))
1362    }
1363}
1364
1365/// DOLLARDE(fractional_dollar, fraction) - Convert fractional dollar to decimal
1366/// Example: DOLLARDE(1.02, 16) = 1.125 (1 and 2/16)
1367#[derive(Debug)]
1368pub struct DollardeFn;
1369impl Function for DollardeFn {
1370    func_caps!(PURE);
1371    fn name(&self) -> &'static str {
1372        "DOLLARDE"
1373    }
1374    fn min_args(&self) -> usize {
1375        2
1376    }
1377    fn arg_schema(&self) -> &'static [ArgSchema] {
1378        use std::sync::LazyLock;
1379        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
1380            vec![
1381                ArgSchema::number_lenient_scalar(), // fractional_dollar
1382                ArgSchema::number_lenient_scalar(), // fraction
1383            ]
1384        });
1385        &SCHEMA[..]
1386    }
1387    fn eval<'a, 'b, 'c>(
1388        &self,
1389        args: &'c [ArgumentHandle<'a, 'b>],
1390        _ctx: &dyn FunctionContext<'b>,
1391    ) -> Result<CalcValue<'b>, ExcelError> {
1392        let fractional_dollar = coerce_num(&args[0])?;
1393        let fraction = coerce_num(&args[1])?.trunc() as i32;
1394
1395        // Validate fraction
1396        if fraction < 1 {
1397            return Ok(CalcValue::Scalar(
1398                LiteralValue::Error(ExcelError::new_num()),
1399            ));
1400        }
1401
1402        // Determine how many decimal places are in the fractional part
1403        // The fractional part represents numerator / fraction
1404        let sign = if fractional_dollar < 0.0 { -1.0 } else { 1.0 };
1405        let abs_value = fractional_dollar.abs();
1406        let integer_part = abs_value.trunc();
1407        let fractional_part = abs_value - integer_part;
1408
1409        // Calculate the number of digits needed to represent the fraction denominator
1410        let digits = (fraction as f64).log10().ceil() as i32;
1411        let multiplier = 10_f64.powi(digits);
1412
1413        // The fractional part is scaled by the multiplier, then divided by the fraction
1414        let numerator = (fractional_part * multiplier).round();
1415        let decimal_fraction = numerator / fraction as f64;
1416
1417        let result = sign * (integer_part + decimal_fraction);
1418        Ok(CalcValue::Scalar(LiteralValue::Number(result)))
1419    }
1420}
1421
1422/// DOLLARFR(decimal_dollar, fraction) - Convert decimal dollar to fractional
1423/// Example: DOLLARFR(1.125, 16) = 1.02
1424#[derive(Debug)]
1425pub struct DollarfrFn;
1426impl Function for DollarfrFn {
1427    func_caps!(PURE);
1428    fn name(&self) -> &'static str {
1429        "DOLLARFR"
1430    }
1431    fn min_args(&self) -> usize {
1432        2
1433    }
1434    fn arg_schema(&self) -> &'static [ArgSchema] {
1435        use std::sync::LazyLock;
1436        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
1437            vec![
1438                ArgSchema::number_lenient_scalar(), // decimal_dollar
1439                ArgSchema::number_lenient_scalar(), // fraction
1440            ]
1441        });
1442        &SCHEMA[..]
1443    }
1444    fn eval<'a, 'b, 'c>(
1445        &self,
1446        args: &'c [ArgumentHandle<'a, 'b>],
1447        _ctx: &dyn FunctionContext<'b>,
1448    ) -> Result<CalcValue<'b>, ExcelError> {
1449        let decimal_dollar = coerce_num(&args[0])?;
1450        let fraction = coerce_num(&args[1])?.trunc() as i32;
1451
1452        // Validate fraction
1453        if fraction < 1 {
1454            return Ok(CalcValue::Scalar(
1455                LiteralValue::Error(ExcelError::new_num()),
1456            ));
1457        }
1458
1459        let sign = if decimal_dollar < 0.0 { -1.0 } else { 1.0 };
1460        let abs_value = decimal_dollar.abs();
1461        let integer_part = abs_value.trunc();
1462        let decimal_part = abs_value - integer_part;
1463
1464        // Convert decimal fraction to fractional representation
1465        // numerator = decimal_part * fraction
1466        let numerator = decimal_part * fraction as f64;
1467
1468        // Calculate the number of digits needed to represent the fraction denominator
1469        let digits = (fraction as f64).log10().ceil() as i32;
1470        let divisor = 10_f64.powi(digits);
1471
1472        // The fractional dollar format puts the numerator after the decimal point
1473        let result = sign * (integer_part + numerator / divisor);
1474        Ok(CalcValue::Scalar(LiteralValue::Number(result)))
1475    }
1476}
1477
1478pub fn register_builtins() {
1479    use std::sync::Arc;
1480    crate::function_registry::register_function(Arc::new(PmtFn));
1481    crate::function_registry::register_function(Arc::new(PvFn));
1482    crate::function_registry::register_function(Arc::new(FvFn));
1483    crate::function_registry::register_function(Arc::new(NpvFn));
1484    crate::function_registry::register_function(Arc::new(NperFn));
1485    crate::function_registry::register_function(Arc::new(RateFn));
1486    crate::function_registry::register_function(Arc::new(IpmtFn));
1487    crate::function_registry::register_function(Arc::new(PpmtFn));
1488    crate::function_registry::register_function(Arc::new(EffectFn));
1489    crate::function_registry::register_function(Arc::new(NominalFn));
1490    crate::function_registry::register_function(Arc::new(IrrFn));
1491    crate::function_registry::register_function(Arc::new(MirrFn));
1492    crate::function_registry::register_function(Arc::new(CumipmtFn));
1493    crate::function_registry::register_function(Arc::new(CumprincFn));
1494    crate::function_registry::register_function(Arc::new(XnpvFn));
1495    crate::function_registry::register_function(Arc::new(XirrFn));
1496    crate::function_registry::register_function(Arc::new(DollardeFn));
1497    crate::function_registry::register_function(Arc::new(DollarfrFn));
1498}
formualizer_eval/builtins/financial/tvm.rs

formualizer_eval/builtins/financial/
tvm.rs
