formualizer_eval/builtins/financial/
bonds.rs

1//! Bond pricing functions: ACCRINT, ACCRINTM, PRICE, YIELD
2
3use crate::args::ArgSchema;
4use crate::builtins::datetime::serial_to_date;
5use crate::function::Function;
6use crate::traits::{ArgumentHandle, CalcValue, FunctionContext};
7use chrono::{Datelike, NaiveDate};
8use formualizer_common::{ExcelError, LiteralValue};
9use formualizer_macros::func_caps;
10
11fn coerce_num(arg: &ArgumentHandle) -> Result<f64, ExcelError> {
12    let v = arg.value()?.into_literal();
13    coerce_literal_num(&v)
14}
15
16fn coerce_literal_num(v: &LiteralValue) -> Result<f64, ExcelError> {
17    match v {
18        LiteralValue::Number(f) => Ok(*f),
19        LiteralValue::Int(i) => Ok(*i as f64),
20        LiteralValue::Boolean(b) => Ok(if *b { 1.0 } else { 0.0 }),
21        LiteralValue::Empty => Ok(0.0),
22        LiteralValue::Error(e) => Err(e.clone()),
23        _ => Err(ExcelError::new_value()),
24    }
25}
26
27/// Day count basis calculation
28/// Returns (num_days, year_basis) for the given basis type
29#[derive(Debug, Clone, Copy, PartialEq)]
30enum DayCountBasis {
31    UsNasd30360 = 0,   // US (NASD) 30/360
32    ActualActual = 1,  // Actual/actual
33    Actual360 = 2,     // Actual/360
34    Actual365 = 3,     // Actual/365
35    European30360 = 4, // European 30/360
36}
37
38impl DayCountBasis {
39    fn from_int(basis: i32) -> Result<Self, ExcelError> {
40        match basis {
41            0 => Ok(DayCountBasis::UsNasd30360),
42            1 => Ok(DayCountBasis::ActualActual),
43            2 => Ok(DayCountBasis::Actual360),
44            3 => Ok(DayCountBasis::Actual365),
45            4 => Ok(DayCountBasis::European30360),
46            _ => Err(ExcelError::new_num()),
47        }
48    }
49}
50
51/// Check if a year is a leap year
52fn is_leap_year(year: i32) -> bool {
53    (year % 4 == 0 && year % 100 != 0) || (year % 400 == 0)
54}
55
56/// Check if a date is the last day of the month
57fn is_last_day_of_month(date: &NaiveDate) -> bool {
58    let next_day = *date + chrono::Duration::days(1);
59    next_day.month() != date.month()
60}
61
62/// Calculate days between two dates using the specified basis
63fn days_between(start: &NaiveDate, end: &NaiveDate, basis: DayCountBasis) -> i32 {
64    match basis {
65        DayCountBasis::UsNasd30360 => days_30_360_us(start, end),
66        DayCountBasis::ActualActual | DayCountBasis::Actual360 | DayCountBasis::Actual365 => {
67            (*end - *start).num_days() as i32
68        }
69        DayCountBasis::European30360 => days_30_360_eu(start, end),
70    }
71}
72
73/// Calculate days using US (NASD) 30/360 method
74fn days_30_360_us(start: &NaiveDate, end: &NaiveDate) -> i32 {
75    let mut sd = start.day() as i32;
76    let sm = start.month() as i32;
77    let sy = start.year();
78
79    let mut ed = end.day() as i32;
80    let em = end.month() as i32;
81    let ey = end.year();
82
83    // Adjust for last day of February
84    let start_is_last_feb = sm == 2 && is_last_day_of_month(start);
85    let end_is_last_feb = em == 2 && is_last_day_of_month(end);
86
87    if start_is_last_feb && end_is_last_feb {
88        ed = 30;
89    }
90    if start_is_last_feb {
91        sd = 30;
92    }
93    if ed == 31 && sd >= 30 {
94        ed = 30;
95    }
96    if sd == 31 {
97        sd = 30;
98    }
99
100    (ey - sy) * 360 + (em - sm) * 30 + (ed - sd)
101}
102
103/// Calculate days using European 30/360 method
104fn days_30_360_eu(start: &NaiveDate, end: &NaiveDate) -> i32 {
105    let mut sd = start.day() as i32;
106    let sm = start.month() as i32;
107    let sy = start.year();
108
109    let mut ed = end.day() as i32;
110    let em = end.month() as i32;
111    let ey = end.year();
112
113    if sd == 31 {
114        sd = 30;
115    }
116    if ed == 31 {
117        ed = 30;
118    }
119
120    (ey - sy) * 360 + (em - sm) * 30 + (ed - sd)
121}
122
123/// Get the annual basis (denominator for year fraction)
124fn annual_basis(basis: DayCountBasis, start: &NaiveDate, end: &NaiveDate) -> f64 {
125    match basis {
126        DayCountBasis::UsNasd30360 | DayCountBasis::European30360 => 360.0,
127        DayCountBasis::Actual360 => 360.0,
128        DayCountBasis::Actual365 => 365.0,
129        DayCountBasis::ActualActual => {
130            // Determine the average year length based on leap years in the period
131            let sy = start.year();
132            let ey = end.year();
133            if sy == ey {
134                if is_leap_year(sy) { 366.0 } else { 365.0 }
135            } else {
136                // Average across years
137                let mut total_days = 0.0;
138                let mut years = 0;
139                for y in sy..=ey {
140                    total_days += if is_leap_year(y) { 366.0 } else { 365.0 };
141                    years += 1;
142                }
143                total_days / years as f64
144            }
145        }
146    }
147}
148
149/// Calculate the year fraction between two dates
150fn year_fraction(start: &NaiveDate, end: &NaiveDate, basis: DayCountBasis) -> f64 {
151    let days = days_between(start, end, basis) as f64;
152    let annual = annual_basis(basis, start, end);
153    days / annual
154}
155
156/// Find the coupon date before settlement date
157fn coupon_date_before(settlement: &NaiveDate, maturity: &NaiveDate, frequency: i32) -> NaiveDate {
158    let months_between_coupons = 12 / frequency;
159    let mut coupon_date = *maturity;
160
161    // Work backwards from maturity to find the coupon date just before settlement
162    while coupon_date >= *settlement {
163        coupon_date = add_months(&coupon_date, -months_between_coupons);
164    }
165    coupon_date
166}
167
168/// Find the coupon date after settlement date
169fn coupon_date_after(settlement: &NaiveDate, maturity: &NaiveDate, frequency: i32) -> NaiveDate {
170    let months_between_coupons = 12 / frequency;
171    let prev_coupon = coupon_date_before(settlement, maturity, frequency);
172    add_months(&prev_coupon, months_between_coupons)
173}
174
175/// Add months to a date, handling end-of-month adjustments
176fn add_months(date: &NaiveDate, months: i32) -> NaiveDate {
177    let total_months = date.year() * 12 + date.month() as i32 - 1 + months;
178    let new_year = total_months / 12;
179    let new_month = (total_months % 12 + 1) as u32;
180
181    // Try to keep the same day, but cap at month's end
182    let mut new_day = date.day();
183    loop {
184        if let Some(d) = NaiveDate::from_ymd_opt(new_year, new_month, new_day) {
185            return d;
186        }
187        new_day -= 1;
188        if new_day == 0 {
189            // Fallback - should never reach here
190            return NaiveDate::from_ymd_opt(new_year, new_month, 1).unwrap();
191        }
192    }
193}
194
195/// Count the number of coupons remaining
196fn coupons_remaining(settlement: &NaiveDate, maturity: &NaiveDate, frequency: i32) -> i32 {
197    let months_between_coupons = 12 / frequency;
198    let mut count = 0;
199    let mut coupon_date = coupon_date_after(settlement, maturity, frequency);
200
201    while coupon_date <= *maturity {
202        count += 1;
203        coupon_date = add_months(&coupon_date, months_between_coupons);
204    }
205    count
206}
207
208/// Returns accrued interest for a coupon-bearing security.
209///
210/// `ACCRINT` calculates interest from either `issue` or the previous coupon date up to
211/// `settlement`, depending on `calc_method`.
212///
213/// # Remarks
214/// - Date inputs are spreadsheet serial dates; `settlement` must be after `issue`.
215/// - `rate` is the annual coupon rate as a decimal (for example, `0.06` for 6%), and `par` is principal amount; both must be positive.
216/// - `frequency` must be `1` (annual), `2` (semiannual), or `4` (quarterly).
217/// - `basis` codes: `0=US(NASD)30/360`, `1=Actual/Actual`, `2=Actual/360`, `3=Actual/365`, `4=European30/360`.
218/// - `calc_method`: non-zero accrues from `issue`; `0` accrues from the previous coupon date.
219/// - Return value is in the same currency units as `par` and is positive for valid positive inputs.
220///
221/// # Examples
222///
223/// ```yaml,sandbox
224/// title: "Accrue from issue date (default calc_method)"
225/// formula: "=ACCRINT(DATE(2024,1,1), DATE(2024,7,1), DATE(2024,7,1), 0.06, 1000, 2, 0)"
226/// expected: 30
227/// ```
228///
229/// ```yaml,sandbox
230/// title: "Accrue from previous coupon (calc_method = 0)"
231/// formula: "=ACCRINT(DATE(2024,1,1), DATE(2024,7,1), DATE(2024,10,1), 0.08, 1000, 2, 0, 0)"
232/// expected: 20
233/// ```
234/// ```yaml,docs
235/// related:
236///   - ACCRINTM
237///   - PRICE
238///   - YIELD
239/// faq:
240///   - q: "When does `calc_method` change the result?"
241///     a: "`calc_method=0` accrues from the previous coupon date; any non-zero value accrues from `issue`."
242///   - q: "Which inputs return `#NUM!`?"
243///     a: "Invalid `basis`, non-positive `rate`/`par`, unsupported `frequency`, or `settlement <= issue` return `#NUM!`."
244/// ```
245#[derive(Debug)]
246pub struct AccrintFn;
247
248/// [formualizer-docgen:schema:start]
249/// Name: ACCRINT
250/// Type: AccrintFn
251/// Min args: 6
252/// Max args: variadic
253/// Variadic: true
254/// Signature: ACCRINT(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5: number@scalar, arg6: number@scalar, arg7: number@scalar, arg8...: number@scalar)
255/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg4{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg5{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg6{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg7{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg8{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
256/// Caps: PURE
257/// [formualizer-docgen:schema:end]
258impl Function for AccrintFn {
259    func_caps!(PURE);
260    fn name(&self) -> &'static str {
261        "ACCRINT"
262    }
263    fn min_args(&self) -> usize {
264        6
265    }
266    fn variadic(&self) -> bool {
267        true
268    }
269    fn arg_schema(&self) -> &'static [ArgSchema] {
270        use std::sync::LazyLock;
271        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
272            vec![
273                ArgSchema::number_lenient_scalar(), // issue
274                ArgSchema::number_lenient_scalar(), // first_interest
275                ArgSchema::number_lenient_scalar(), // settlement
276                ArgSchema::number_lenient_scalar(), // rate
277                ArgSchema::number_lenient_scalar(), // par
278                ArgSchema::number_lenient_scalar(), // frequency
279                ArgSchema::number_lenient_scalar(), // basis (optional)
280                ArgSchema::number_lenient_scalar(), // calc_method (optional)
281            ]
282        });
283        &SCHEMA[..]
284    }
285    fn eval<'a, 'b, 'c>(
286        &self,
287        args: &'c [ArgumentHandle<'a, 'b>],
288        _ctx: &dyn FunctionContext<'b>,
289    ) -> Result<CalcValue<'b>, ExcelError> {
290        // Check minimum required arguments
291        if args.len() < 6 {
292            return Ok(CalcValue::Scalar(LiteralValue::Error(
293                ExcelError::new_value(),
294            )));
295        }
296
297        let issue_serial = coerce_num(&args[0])?;
298        let first_interest_serial = coerce_num(&args[1])?;
299        let settlement_serial = coerce_num(&args[2])?;
300        let rate = coerce_num(&args[3])?;
301        let par = coerce_num(&args[4])?;
302        let frequency = coerce_num(&args[5])?.trunc() as i32;
303        let basis_int = if args.len() > 6 {
304            coerce_num(&args[6])?.trunc() as i32
305        } else {
306            0
307        };
308        let calc_method = if args.len() > 7 {
309            coerce_num(&args[7])?.trunc() as i32
310        } else {
311            1
312        };
313
314        // Validate inputs
315        if rate <= 0.0 || par <= 0.0 {
316            return Ok(CalcValue::Scalar(
317                LiteralValue::Error(ExcelError::new_num()),
318            ));
319        }
320        if frequency != 1 && frequency != 2 && frequency != 4 {
321            return Ok(CalcValue::Scalar(
322                LiteralValue::Error(ExcelError::new_num()),
323            ));
324        }
325
326        let basis = DayCountBasis::from_int(basis_int)?;
327
328        let issue = serial_to_date(issue_serial)?;
329        let first_interest = serial_to_date(first_interest_serial)?;
330        let settlement = serial_to_date(settlement_serial)?;
331
332        // settlement must be after issue
333        if settlement <= issue {
334            return Ok(CalcValue::Scalar(
335                LiteralValue::Error(ExcelError::new_num()),
336            ));
337        }
338
339        // Calculate accrued interest
340        // If calc_method is TRUE (or 1), calculate from issue to settlement
341        // If calc_method is FALSE (or 0), calculate from last coupon to settlement
342        let accrued_interest = if calc_method != 0 {
343            // Calculate from issue date to settlement date
344            // ACCRINT = par * rate * year_fraction(issue, settlement)
345            let yf = year_fraction(&issue, &settlement, basis);
346            par * rate * yf
347        } else {
348            // Calculate from last coupon date to settlement
349            let prev_coupon = coupon_date_before(&settlement, &first_interest, frequency);
350            let start_date = if prev_coupon < issue {
351                issue
352            } else {
353                prev_coupon
354            };
355            let yf = year_fraction(&start_date, &settlement, basis);
356            par * rate * yf
357        };
358
359        Ok(CalcValue::Scalar(LiteralValue::Number(accrued_interest)))
360    }
361}
362
363/// Returns accrued interest for a security that pays interest at maturity.
364///
365/// `ACCRINTM` accrues from `issue` to `settlement` with no periodic coupon schedule.
366///
367/// # Remarks
368/// - Date inputs are spreadsheet serial dates and must satisfy `settlement > issue`.
369/// - `rate` is an annual decimal rate (for example, `0.05` for 5%), and `par` must be positive.
370/// - `basis` codes: `0=US(NASD)30/360`, `1=Actual/Actual`, `2=Actual/360`, `3=Actual/365`, `4=European30/360`.
371/// - Return value is accrued interest amount in the same currency units as `par`.
372/// - With positive `rate` and `par`, the result is positive.
373///
374/// # Examples
375///
376/// ```yaml,sandbox
377/// title: "One full 30/360 year"
378/// formula: "=ACCRINTM(DATE(2024,1,1), DATE(2025,1,1), 0.05, 1000, 0)"
379/// expected: 50
380/// ```
381///
382/// ```yaml,sandbox
383/// title: "European 30/360 half-year accrual"
384/// formula: "=ACCRINTM(DATE(2024,2,28), DATE(2024,8,28), 0.04, 1000, 4)"
385/// expected: 20
386/// ```
387/// ```yaml,docs
388/// related:
389///   - ACCRINT
390///   - PRICE
391///   - YIELD
392/// faq:
393///   - q: "Does `ACCRINTM` use coupon frequency?"
394///     a: "No. It accrues directly from `issue` to `settlement` with no periodic coupon schedule."
395///   - q: "What causes `#NUM!`?"
396///     a: "Invalid `basis`, non-positive `rate`/`par`, or `settlement <= issue`."
397/// ```
398#[derive(Debug)]
399pub struct AccrintmFn;
400
401/// [formualizer-docgen:schema:start]
402/// Name: ACCRINTM
403/// Type: AccrintmFn
404/// Min args: 4
405/// Max args: variadic
406/// Variadic: true
407/// Signature: ACCRINTM(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5...: number@scalar)
408/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg4{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg5{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
409/// Caps: PURE
410/// [formualizer-docgen:schema:end]
411impl Function for AccrintmFn {
412    func_caps!(PURE);
413    fn name(&self) -> &'static str {
414        "ACCRINTM"
415    }
416    fn min_args(&self) -> usize {
417        4
418    }
419    fn variadic(&self) -> bool {
420        true
421    }
422    fn arg_schema(&self) -> &'static [ArgSchema] {
423        use std::sync::LazyLock;
424        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
425            vec![
426                ArgSchema::number_lenient_scalar(), // issue
427                ArgSchema::number_lenient_scalar(), // settlement
428                ArgSchema::number_lenient_scalar(), // rate
429                ArgSchema::number_lenient_scalar(), // par
430                ArgSchema::number_lenient_scalar(), // basis (optional)
431            ]
432        });
433        &SCHEMA[..]
434    }
435    fn eval<'a, 'b, 'c>(
436        &self,
437        args: &'c [ArgumentHandle<'a, 'b>],
438        _ctx: &dyn FunctionContext<'b>,
439    ) -> Result<CalcValue<'b>, ExcelError> {
440        // Check minimum required arguments
441        if args.len() < 4 {
442            return Ok(CalcValue::Scalar(LiteralValue::Error(
443                ExcelError::new_value(),
444            )));
445        }
446
447        let issue_serial = coerce_num(&args[0])?;
448        let settlement_serial = coerce_num(&args[1])?;
449        let rate = coerce_num(&args[2])?;
450        let par = coerce_num(&args[3])?;
451        let basis_int = if args.len() > 4 {
452            coerce_num(&args[4])?.trunc() as i32
453        } else {
454            0
455        };
456
457        // Validate inputs
458        if rate <= 0.0 || par <= 0.0 {
459            return Ok(CalcValue::Scalar(
460                LiteralValue::Error(ExcelError::new_num()),
461            ));
462        }
463
464        let basis = DayCountBasis::from_int(basis_int)?;
465
466        let issue = serial_to_date(issue_serial)?;
467        let settlement = serial_to_date(settlement_serial)?;
468
469        // settlement must be after issue
470        if settlement <= issue {
471            return Ok(CalcValue::Scalar(
472                LiteralValue::Error(ExcelError::new_num()),
473            ));
474        }
475
476        // ACCRINTM = par * rate * year_fraction(issue, settlement)
477        let yf = year_fraction(&issue, &settlement, basis);
478        let accrued_interest = par * rate * yf;
479
480        Ok(CalcValue::Scalar(LiteralValue::Number(accrued_interest)))
481    }
482}
483
484/// Returns clean price per 100 face value for a coupon-paying security.
485///
486/// `PRICE` discounts remaining coupons and redemption to settlement and subtracts accrued
487/// coupon interest according to the chosen day-count basis.
488///
489/// # Remarks
490/// - Date inputs are spreadsheet serial dates and must satisfy `maturity > settlement`.
491/// - `rate` (coupon) and `yld` (yield) are annual decimal rates; `redemption` is amount paid per 100 face value at maturity.
492/// - `frequency` must be `1` (annual), `2` (semiannual), or `4` (quarterly).
493/// - `basis` codes: `0=US(NASD)30/360`, `1=Actual/Actual`, `2=Actual/360`, `3=Actual/365`, `4=European30/360`.
494/// - Return value is quoted per 100 face value; positive inputs usually produce a positive price.
495/// - Coupon schedule is derived by stepping backward from `maturity`, with end-of-month adjustment behavior in month arithmetic.
496///
497/// # Examples
498///
499/// ```yaml,sandbox
500/// title: "Single remaining coupon period"
501/// formula: "=PRICE(DATE(2024,4,1), DATE(2024,7,1), 0.06, 0.05, 100, 2, 0)"
502/// expected: 100.2283950617284
503/// ```
504///
505/// ```yaml,sandbox
506/// title: "Par bond when coupon rate equals yield"
507/// formula: "=PRICE(DATE(2024,3,1), DATE(2026,3,1), 0.05, 0.05, 100, 2, 0)"
508/// expected: 100
509/// ```
510/// ```yaml,docs
511/// related:
512///   - YIELD
513///   - ACCRINT
514///   - ACCRINTM
515/// faq:
516///   - q: "Why is `PRICE` quoted per 100 even if my bond face value differs?"
517///     a: "This implementation follows Excel quoting conventions and returns clean price per 100 face value."
518///   - q: "Which domain checks return `#NUM!`?"
519///     a: "`maturity <= settlement`, invalid `basis`, unsupported `frequency`, negative `rate`/`yld`, or non-positive `redemption`."
520/// ```
521#[derive(Debug)]
522pub struct PriceFn;
523
524/// [formualizer-docgen:schema:start]
525/// Name: PRICE
526/// Type: PriceFn
527/// Min args: 6
528/// Max args: variadic
529/// Variadic: true
530/// Signature: PRICE(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5: number@scalar, arg6: number@scalar, arg7...: number@scalar)
531/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg4{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg5{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg6{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg7{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
532/// Caps: PURE
533/// [formualizer-docgen:schema:end]
534impl Function for PriceFn {
535    func_caps!(PURE);
536    fn name(&self) -> &'static str {
537        "PRICE"
538    }
539    fn min_args(&self) -> usize {
540        6
541    }
542    fn variadic(&self) -> bool {
543        true
544    }
545    fn arg_schema(&self) -> &'static [ArgSchema] {
546        use std::sync::LazyLock;
547        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
548            vec![
549                ArgSchema::number_lenient_scalar(), // settlement
550                ArgSchema::number_lenient_scalar(), // maturity
551                ArgSchema::number_lenient_scalar(), // rate (coupon rate)
552                ArgSchema::number_lenient_scalar(), // yld (yield)
553                ArgSchema::number_lenient_scalar(), // redemption
554                ArgSchema::number_lenient_scalar(), // frequency
555                ArgSchema::number_lenient_scalar(), // basis (optional)
556            ]
557        });
558        &SCHEMA[..]
559    }
560    fn eval<'a, 'b, 'c>(
561        &self,
562        args: &'c [ArgumentHandle<'a, 'b>],
563        _ctx: &dyn FunctionContext<'b>,
564    ) -> Result<CalcValue<'b>, ExcelError> {
565        // Check minimum required arguments
566        if args.len() < 6 {
567            return Ok(CalcValue::Scalar(LiteralValue::Error(
568                ExcelError::new_value(),
569            )));
570        }
571
572        let settlement_serial = coerce_num(&args[0])?;
573        let maturity_serial = coerce_num(&args[1])?;
574        let rate = coerce_num(&args[2])?;
575        let yld = coerce_num(&args[3])?;
576        let redemption = coerce_num(&args[4])?;
577        let frequency = coerce_num(&args[5])?.trunc() as i32;
578        let basis_int = if args.len() > 6 {
579            coerce_num(&args[6])?.trunc() as i32
580        } else {
581            0
582        };
583
584        // Validate inputs
585        if rate < 0.0 || yld < 0.0 || redemption <= 0.0 {
586            return Ok(CalcValue::Scalar(
587                LiteralValue::Error(ExcelError::new_num()),
588            ));
589        }
590        if frequency != 1 && frequency != 2 && frequency != 4 {
591            return Ok(CalcValue::Scalar(
592                LiteralValue::Error(ExcelError::new_num()),
593            ));
594        }
595
596        let basis = DayCountBasis::from_int(basis_int)?;
597
598        let settlement = serial_to_date(settlement_serial)?;
599        let maturity = serial_to_date(maturity_serial)?;
600
601        // maturity must be after settlement
602        if maturity <= settlement {
603            return Ok(CalcValue::Scalar(
604                LiteralValue::Error(ExcelError::new_num()),
605            ));
606        }
607
608        let price = calculate_price(
609            &settlement,
610            &maturity,
611            rate,
612            yld,
613            redemption,
614            frequency,
615            basis,
616        );
617        Ok(CalcValue::Scalar(LiteralValue::Number(price)))
618    }
619}
620
621/// Calculate bond price using standard bond pricing formula
622fn calculate_price(
623    settlement: &NaiveDate,
624    maturity: &NaiveDate,
625    rate: f64,
626    yld: f64,
627    redemption: f64,
628    frequency: i32,
629    basis: DayCountBasis,
630) -> f64 {
631    let n = coupons_remaining(settlement, maturity, frequency);
632    let coupon = 100.0 * rate / frequency as f64;
633
634    // Find previous and next coupon dates
635    let next_coupon = coupon_date_after(settlement, maturity, frequency);
636    let prev_coupon = coupon_date_before(settlement, maturity, frequency);
637
638    // Calculate fraction of period from settlement to next coupon
639    let days_to_next = days_between(settlement, &next_coupon, basis) as f64;
640    let days_in_period = days_between(&prev_coupon, &next_coupon, basis) as f64;
641
642    let dsn = if days_in_period > 0.0 {
643        days_to_next / days_in_period
644    } else {
645        0.0
646    };
647
648    let yld_per_period = yld / frequency as f64;
649
650    if n == 1 {
651        // Short first period (single coupon remaining)
652        // Price = (redemption + coupon) / (1 + dsn * yld_per_period) - (1 - dsn) * coupon
653        (redemption + coupon) / (1.0 + dsn * yld_per_period) - (1.0 - dsn) * coupon
654    } else {
655        // Multiple coupons remaining
656        // Price = sum of discounted coupons + discounted redemption - accrued interest
657        let discount_factor = 1.0 + yld_per_period;
658
659        // Discount factor for first coupon (fractional period)
660        let first_discount = discount_factor.powf(dsn);
661
662        // Present value of coupon payments
663        let mut pv_coupons = 0.0;
664        for k in 0..n {
665            let discount = first_discount * discount_factor.powi(k);
666            pv_coupons += coupon / discount;
667        }
668
669        // Present value of redemption
670        let pv_redemption = redemption / (first_discount * discount_factor.powi(n - 1));
671
672        // Accrued interest (negative because we subtract it)
673        let accrued = (1.0 - dsn) * coupon;
674
675        pv_coupons + pv_redemption - accrued
676    }
677}
678
679/// Returns annual yield for a coupon-paying security from its market price.
680///
681/// `YIELD` solves for the annual rate that makes `PRICE(...)` match the input `pr`.
682///
683/// # Remarks
684/// - Date inputs are spreadsheet serial dates and must satisfy `maturity > settlement`.
685/// - `rate` is coupon rate (annual decimal), `pr` is price per 100 face value, and `redemption` is redemption per 100; `pr` and `redemption` must be positive.
686/// - `frequency` must be `1` (annual), `2` (semiannual), or `4` (quarterly).
687/// - `basis` codes: `0=US(NASD)30/360`, `1=Actual/Actual`, `2=Actual/360`, `3=Actual/365`, `4=European30/360`.
688/// - Result is an annualized decimal yield (for example, `0.05` means 5%).
689/// - This implementation uses Newton-Raphson iteration; if it cannot converge, it returns `#NUM!`.
690///
691/// # Examples
692///
693/// ```yaml,sandbox
694/// title: "Par price implies coupon-rate yield"
695/// formula: "=YIELD(DATE(2024,3,1), DATE(2026,3,1), 0.05, 100, 100, 2, 0)"
696/// expected: 0.05
697/// ```
698///
699/// ```yaml,sandbox
700/// title: "Yield recovered from a discounted price"
701/// formula: "=YIELD(DATE(2024,2,15), DATE(2027,2,15), 0.05, 97.2914042780609, 100, 2, 0)"
702/// expected: 0.06
703/// ```
704/// ```yaml,docs
705/// related:
706///   - PRICE
707///   - ACCRINT
708///   - ACCRINTM
709/// faq:
710///   - q: "What does the returned `YIELD` represent?"
711///     a: "It is an annualized decimal yield (for example, `0.06` means 6% per year)."
712///   - q: "When does `YIELD` return `#NUM!` besides invalid inputs?"
713///     a: "The Newton-Raphson solve can fail to converge or hit an unstable derivative; in those cases it returns `#NUM!`."
714/// ```
715#[derive(Debug)]
716pub struct YieldFn;
717
718/// [formualizer-docgen:schema:start]
719/// Name: YIELD
720/// Type: YieldFn
721/// Min args: 6
722/// Max args: variadic
723/// Variadic: true
724/// Signature: YIELD(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5: number@scalar, arg6: number@scalar, arg7...: number@scalar)
725/// Arg schema: arg1{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg2{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg3{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg4{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg5{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg6{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}; arg7{kinds=number,required=true,shape=scalar,by_ref=false,coercion=NumberLenientText,max=None,repeating=None,default=false}
726/// Caps: PURE
727/// [formualizer-docgen:schema:end]
728impl Function for YieldFn {
729    func_caps!(PURE);
730    fn name(&self) -> &'static str {
731        "YIELD"
732    }
733    fn min_args(&self) -> usize {
734        6
735    }
736    fn variadic(&self) -> bool {
737        true
738    }
739    fn arg_schema(&self) -> &'static [ArgSchema] {
740        use std::sync::LazyLock;
741        static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
742            vec![
743                ArgSchema::number_lenient_scalar(), // settlement
744                ArgSchema::number_lenient_scalar(), // maturity
745                ArgSchema::number_lenient_scalar(), // rate (coupon rate)
746                ArgSchema::number_lenient_scalar(), // pr (price)
747                ArgSchema::number_lenient_scalar(), // redemption
748                ArgSchema::number_lenient_scalar(), // frequency
749                ArgSchema::number_lenient_scalar(), // basis (optional)
750            ]
751        });
752        &SCHEMA[..]
753    }
754    fn eval<'a, 'b, 'c>(
755        &self,
756        args: &'c [ArgumentHandle<'a, 'b>],
757        _ctx: &dyn FunctionContext<'b>,
758    ) -> Result<CalcValue<'b>, ExcelError> {
759        // Check minimum required arguments
760        if args.len() < 6 {
761            return Ok(CalcValue::Scalar(LiteralValue::Error(
762                ExcelError::new_value(),
763            )));
764        }
765
766        let settlement_serial = coerce_num(&args[0])?;
767        let maturity_serial = coerce_num(&args[1])?;
768        let rate = coerce_num(&args[2])?;
769        let pr = coerce_num(&args[3])?;
770        let redemption = coerce_num(&args[4])?;
771        let frequency = coerce_num(&args[5])?.trunc() as i32;
772        let basis_int = if args.len() > 6 {
773            coerce_num(&args[6])?.trunc() as i32
774        } else {
775            0
776        };
777
778        // Validate inputs
779        if rate < 0.0 || pr <= 0.0 || redemption <= 0.0 {
780            return Ok(CalcValue::Scalar(
781                LiteralValue::Error(ExcelError::new_num()),
782            ));
783        }
784        if frequency != 1 && frequency != 2 && frequency != 4 {
785            return Ok(CalcValue::Scalar(
786                LiteralValue::Error(ExcelError::new_num()),
787            ));
788        }
789
790        let basis = DayCountBasis::from_int(basis_int)?;
791
792        let settlement = serial_to_date(settlement_serial)?;
793        let maturity = serial_to_date(maturity_serial)?;
794
795        // maturity must be after settlement
796        if maturity <= settlement {
797            return Ok(CalcValue::Scalar(
798                LiteralValue::Error(ExcelError::new_num()),
799            ));
800        }
801
802        // Use Newton-Raphson to find yield where price = target price
803        let yld = calculate_yield(
804            &settlement,
805            &maturity,
806            rate,
807            pr,
808            redemption,
809            frequency,
810            basis,
811        );
812
813        match yld {
814            Some(y) => Ok(CalcValue::Scalar(LiteralValue::Number(y))),
815            None => Ok(CalcValue::Scalar(
816                LiteralValue::Error(ExcelError::new_num()),
817            )),
818        }
819    }
820}
821
822/// Calculate yield using Newton-Raphson iteration
823fn calculate_yield(
824    settlement: &NaiveDate,
825    maturity: &NaiveDate,
826    rate: f64,
827    target_price: f64,
828    redemption: f64,
829    frequency: i32,
830    basis: DayCountBasis,
831) -> Option<f64> {
832    const MAX_ITER: i32 = 100;
833    const EPSILON: f64 = 1e-10;
834
835    // Initial guess based on coupon rate
836    let mut yld = rate;
837    if yld == 0.0 {
838        yld = 0.05; // Default guess if rate is 0
839    }
840
841    for _ in 0..MAX_ITER {
842        let price = calculate_price(
843            settlement, maturity, rate, yld, redemption, frequency, basis,
844        );
845        let diff = price - target_price;
846
847        if diff.abs() < EPSILON {
848            return Some(yld);
849        }
850
851        // Calculate derivative numerically
852        let delta = 0.0001;
853        let price_up = calculate_price(
854            settlement,
855            maturity,
856            rate,
857            yld + delta,
858            redemption,
859            frequency,
860            basis,
861        );
862        let derivative = (price_up - price) / delta;
863
864        if derivative.abs() < EPSILON {
865            return None;
866        }
867
868        let new_yld = yld - diff / derivative;
869
870        // Prevent yield from going too negative
871        if new_yld < -0.99 {
872            yld = -0.99;
873        } else {
874            yld = new_yld;
875        }
876
877        // Prevent yield from going too high
878        if yld > 10.0 {
879            yld = 10.0;
880        }
881    }
882
883    // If close enough after max iterations, return the result
884    let final_price = calculate_price(
885        settlement, maturity, rate, yld, redemption, frequency, basis,
886    );
887    if (final_price - target_price).abs() < 0.01 {
888        Some(yld)
889    } else {
890        None
891    }
892}
893
894pub fn register_builtins() {
895    use std::sync::Arc;
896    crate::function_registry::register_function(Arc::new(AccrintFn));
897    crate::function_registry::register_function(Arc::new(AccrintmFn));
898    crate::function_registry::register_function(Arc::new(PriceFn));
899    crate::function_registry::register_function(Arc::new(YieldFn));
900}
formualizer_eval/builtins/financial/bonds.rs

formualizer_eval/builtins/financial/
bonds.rs
