//! Bond pricing functions: ACCRINT, ACCRINTM, PRICE, YIELD
use crate::args::ArgSchema;
use crate::builtins::datetime::serial_to_date;
use crate::function::Function;
use crate::traits::{ArgumentHandle, CalcValue, FunctionContext};
use chrono::{Datelike, NaiveDate};
use formualizer_common::{ExcelError, LiteralValue};
use formualizer_macros::func_caps;
fn coerce_num(arg: &ArgumentHandle) -> Result<f64, ExcelError> {
let v = arg.value()?.into_literal();
coerce_literal_num(&v)
}
fn coerce_literal_num(v: &LiteralValue) -> Result<f64, ExcelError> {
match v {
LiteralValue::Number(f) => Ok(*f),
LiteralValue::Int(i) => Ok(*i as f64),
LiteralValue::Boolean(b) => Ok(if *b { 1.0 } else { 0.0 }),
LiteralValue::Empty => Ok(0.0),
LiteralValue::Error(e) => Err(e.clone()),
_ => Err(ExcelError::new_value()),
}
}
/// Day count basis calculation
/// Returns (num_days, year_basis) for the given basis type
#[derive(Debug, Clone, Copy, PartialEq)]
enum DayCountBasis {
UsNasd30360 = 0, // US (NASD) 30/360
ActualActual = 1, // Actual/actual
Actual360 = 2, // Actual/360
Actual365 = 3, // Actual/365
European30360 = 4, // European 30/360
}
impl DayCountBasis {
fn from_int(basis: i32) -> Result<Self, ExcelError> {
match basis {
0 => Ok(DayCountBasis::UsNasd30360),
1 => Ok(DayCountBasis::ActualActual),
2 => Ok(DayCountBasis::Actual360),
3 => Ok(DayCountBasis::Actual365),
4 => Ok(DayCountBasis::European30360),
_ => Err(ExcelError::new_num()),
}
}
}
/// Check if a year is a leap year
fn is_leap_year(year: i32) -> bool {
(year % 4 == 0 && year % 100 != 0) || (year % 400 == 0)
}
/// Check if a date is the last day of the month
fn is_last_day_of_month(date: &NaiveDate) -> bool {
let next_day = *date + chrono::Duration::days(1);
next_day.month() != date.month()
}
/// Calculate days between two dates using the specified basis
fn days_between(start: &NaiveDate, end: &NaiveDate, basis: DayCountBasis) -> i32 {
match basis {
DayCountBasis::UsNasd30360 => days_30_360_us(start, end),
DayCountBasis::ActualActual | DayCountBasis::Actual360 | DayCountBasis::Actual365 => {
(*end - *start).num_days() as i32
}
DayCountBasis::European30360 => days_30_360_eu(start, end),
}
}
/// Calculate days using US (NASD) 30/360 method
fn days_30_360_us(start: &NaiveDate, end: &NaiveDate) -> i32 {
let mut sd = start.day() as i32;
let sm = start.month() as i32;
let sy = start.year();
let mut ed = end.day() as i32;
let em = end.month() as i32;
let ey = end.year();
// Adjust for last day of February
let start_is_last_feb = sm == 2 && is_last_day_of_month(start);
let end_is_last_feb = em == 2 && is_last_day_of_month(end);
if start_is_last_feb && end_is_last_feb {
ed = 30;
}
if start_is_last_feb {
sd = 30;
}
if ed == 31 && sd >= 30 {
ed = 30;
}
if sd == 31 {
sd = 30;
}
(ey - sy) * 360 + (em - sm) * 30 + (ed - sd)
}
/// Calculate days using European 30/360 method
fn days_30_360_eu(start: &NaiveDate, end: &NaiveDate) -> i32 {
let mut sd = start.day() as i32;
let sm = start.month() as i32;
let sy = start.year();
let mut ed = end.day() as i32;
let em = end.month() as i32;
let ey = end.year();
if sd == 31 {
sd = 30;
}
if ed == 31 {
ed = 30;
}
(ey - sy) * 360 + (em - sm) * 30 + (ed - sd)
}
/// Calculate the year fraction between two dates.
///
/// For Actual/Actual we mirror the prorated year-boundary behavior used by YEARFRAC
/// rather than averaging year lengths across the entire span. This keeps bond accrual
/// math aligned with the engine's date-part functions for cross-year ranges.
fn year_fraction(start: &NaiveDate, end: &NaiveDate, basis: DayCountBasis) -> f64 {
if start == end {
return 0.0;
}
let (s, e, sign) = if start <= end {
(start, end, 1.0)
} else {
(end, start, -1.0)
};
let frac = match basis {
DayCountBasis::UsNasd30360 | DayCountBasis::European30360 => {
days_between(s, e, basis) as f64 / 360.0
}
DayCountBasis::Actual360 => ((*e - *s).num_days() as f64) / 360.0,
DayCountBasis::Actual365 => ((*e - *s).num_days() as f64) / 365.0,
DayCountBasis::ActualActual => {
let actual_days = (*e - *s).num_days() as f64;
if s.year() == e.year() {
actual_days / if is_leap_year(s.year()) { 366.0 } else { 365.0 }
} else {
let start_year_end = NaiveDate::from_ymd_opt(s.year() + 1, 1, 1).unwrap();
let end_year_start = NaiveDate::from_ymd_opt(e.year(), 1, 1).unwrap();
let mut out = (start_year_end - *s).num_days() as f64
/ if is_leap_year(s.year()) { 366.0 } else { 365.0 };
for _year in (s.year() + 1)..e.year() {
out += 1.0;
}
out + (*e - end_year_start).num_days() as f64
/ if is_leap_year(e.year()) { 366.0 } else { 365.0 }
}
}
};
sign * frac
}
/// Find the coupon date before settlement date
fn coupon_date_before(settlement: &NaiveDate, maturity: &NaiveDate, frequency: i32) -> NaiveDate {
let months_between_coupons = 12 / frequency;
let mut coupon_date = *maturity;
// Work backwards from maturity to find the coupon date just before settlement
while coupon_date >= *settlement {
coupon_date = add_months(&coupon_date, -months_between_coupons);
}
coupon_date
}
/// Find the coupon date after settlement date
fn coupon_date_after(settlement: &NaiveDate, maturity: &NaiveDate, frequency: i32) -> NaiveDate {
let months_between_coupons = 12 / frequency;
let prev_coupon = coupon_date_before(settlement, maturity, frequency);
add_months(&prev_coupon, months_between_coupons)
}
/// Add months to a date, handling end-of-month adjustments
fn add_months(date: &NaiveDate, months: i32) -> NaiveDate {
let total_months = date.year() * 12 + date.month() as i32 - 1 + months;
let new_year = total_months / 12;
let new_month = (total_months % 12 + 1) as u32;
// Try to keep the same day, but cap at month's end
let mut new_day = date.day();
loop {
if let Some(d) = NaiveDate::from_ymd_opt(new_year, new_month, new_day) {
return d;
}
new_day -= 1;
if new_day == 0 {
// Fallback - should never reach here
return NaiveDate::from_ymd_opt(new_year, new_month, 1).unwrap();
}
}
}
/// Count the number of coupons remaining
fn coupons_remaining(settlement: &NaiveDate, maturity: &NaiveDate, frequency: i32) -> i32 {
let months_between_coupons = 12 / frequency;
let mut count = 0;
let mut coupon_date = coupon_date_after(settlement, maturity, frequency);
while coupon_date <= *maturity {
count += 1;
coupon_date = add_months(&coupon_date, months_between_coupons);
}
count
}
/// Returns accrued interest for a coupon-bearing security.
///
/// `ACCRINT` calculates interest from either `issue` or the previous coupon date up to
/// `settlement`, depending on `calc_method`.
///
/// # Remarks
/// - Date inputs are spreadsheet serial dates; `settlement` must be after `issue`.
/// - `rate` is the annual coupon rate as a decimal (for example, `0.06` for 6%), and `par` is principal amount; both must be positive.
/// - `frequency` must be `1` (annual), `2` (semiannual), or `4` (quarterly).
/// - `basis` codes: `0=US(NASD)30/360`, `1=Actual/Actual`, `2=Actual/360`, `3=Actual/365`, `4=European30/360`.
/// - `calc_method`: non-zero accrues from `issue`; `0` accrues from the previous coupon date.
/// - Return value is in the same currency units as `par` and is positive for valid positive inputs.
///
/// # Examples
///
/// ```yaml,sandbox
/// title: "Accrue from issue date (default calc_method)"
/// formula: "=ACCRINT(DATE(2024,1,1), DATE(2024,7,1), DATE(2024,7,1), 0.06, 1000, 2, 0)"
/// expected: 30
/// ```
///
/// ```yaml,sandbox
/// title: "Accrue from previous coupon (calc_method = 0)"
/// formula: "=ACCRINT(DATE(2024,1,1), DATE(2024,7,1), DATE(2024,10,1), 0.08, 1000, 2, 0, 0)"
/// expected: 20
/// ```
/// ```yaml,docs
/// related:
/// - ACCRINTM
/// - PRICE
/// - YIELD
/// faq:
/// - q: "When does `calc_method` change the result?"
/// a: "`calc_method=0` accrues from the previous coupon date; any non-zero value accrues from `issue`."
/// - q: "Which inputs return `#NUM!`?"
/// a: "Invalid `basis`, non-positive `rate`/`par`, unsupported `frequency`, or `settlement <= issue` return `#NUM!`."
/// ```
#[derive(Debug)]
pub struct AccrintFn;
/// [formualizer-docgen:schema:start]
/// Name: ACCRINT
/// Type: AccrintFn
/// Min args: 6
/// Max args: variadic
/// Variadic: true
/// 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)
/// 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}
/// Caps: PURE
/// [formualizer-docgen:schema:end]
impl Function for AccrintFn {
func_caps!(PURE);
fn name(&self) -> &'static str {
"ACCRINT"
}
fn min_args(&self) -> usize {
6
}
fn variadic(&self) -> bool {
true
}
fn arg_schema(&self) -> &'static [ArgSchema] {
use std::sync::LazyLock;
static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
vec![
ArgSchema::number_lenient_scalar(), // issue
ArgSchema::number_lenient_scalar(), // first_interest
ArgSchema::number_lenient_scalar(), // settlement
ArgSchema::number_lenient_scalar(), // rate
ArgSchema::number_lenient_scalar(), // par
ArgSchema::number_lenient_scalar(), // frequency
ArgSchema::number_lenient_scalar(), // basis (optional)
ArgSchema::number_lenient_scalar(), // calc_method (optional)
]
});
&SCHEMA[..]
}
fn eval<'a, 'b, 'c>(
&self,
args: &'c [ArgumentHandle<'a, 'b>],
_ctx: &dyn FunctionContext<'b>,
) -> Result<CalcValue<'b>, ExcelError> {
// Check minimum required arguments
if args.len() < 6 {
return Ok(CalcValue::Scalar(LiteralValue::Error(
ExcelError::new_value(),
)));
}
let issue_serial = coerce_num(&args[0])?;
let first_interest_serial = coerce_num(&args[1])?;
let settlement_serial = coerce_num(&args[2])?;
let rate = coerce_num(&args[3])?;
let par = coerce_num(&args[4])?;
let frequency = coerce_num(&args[5])?.trunc() as i32;
let basis_int = if args.len() > 6 {
coerce_num(&args[6])?.trunc() as i32
} else {
0
};
let calc_method = if args.len() > 7 {
coerce_num(&args[7])?.trunc() as i32
} else {
1
};
// Validate inputs
if rate <= 0.0 || par <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
if frequency != 1 && frequency != 2 && frequency != 4 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let basis = DayCountBasis::from_int(basis_int)?;
let issue = serial_to_date(issue_serial)?;
let first_interest = serial_to_date(first_interest_serial)?;
let settlement = serial_to_date(settlement_serial)?;
// settlement must be after issue
if settlement <= issue {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
// Calculate accrued interest
// If calc_method is TRUE (or 1), calculate from issue to settlement
// If calc_method is FALSE (or 0), calculate from last coupon to settlement
let accrued_interest = if calc_method != 0 {
// Calculate from issue date to settlement date
// ACCRINT = par * rate * year_fraction(issue, settlement)
let yf = year_fraction(&issue, &settlement, basis);
par * rate * yf
} else {
// Calculate from last coupon date to settlement
let prev_coupon = coupon_date_before(&settlement, &first_interest, frequency);
let start_date = if prev_coupon < issue {
issue
} else {
prev_coupon
};
let yf = year_fraction(&start_date, &settlement, basis);
par * rate * yf
};
Ok(CalcValue::Scalar(LiteralValue::Number(accrued_interest)))
}
}
/// Returns accrued interest for a security that pays interest at maturity.
///
/// `ACCRINTM` accrues from `issue` to `settlement` with no periodic coupon schedule.
///
/// # Remarks
/// - Date inputs are spreadsheet serial dates and must satisfy `settlement > issue`.
/// - `rate` is an annual decimal rate (for example, `0.05` for 5%), and `par` must be positive.
/// - `basis` codes: `0=US(NASD)30/360`, `1=Actual/Actual`, `2=Actual/360`, `3=Actual/365`, `4=European30/360`.
/// - Return value is accrued interest amount in the same currency units as `par`.
/// - With positive `rate` and `par`, the result is positive.
///
/// # Examples
///
/// ```yaml,sandbox
/// title: "One full 30/360 year"
/// formula: "=ACCRINTM(DATE(2024,1,1), DATE(2025,1,1), 0.05, 1000, 0)"
/// expected: 50
/// ```
///
/// ```yaml,sandbox
/// title: "European 30/360 half-year accrual"
/// formula: "=ACCRINTM(DATE(2024,2,28), DATE(2024,8,28), 0.04, 1000, 4)"
/// expected: 20
/// ```
/// ```yaml,docs
/// related:
/// - ACCRINT
/// - PRICE
/// - YIELD
/// faq:
/// - q: "Does `ACCRINTM` use coupon frequency?"
/// a: "No. It accrues directly from `issue` to `settlement` with no periodic coupon schedule."
/// - q: "What causes `#NUM!`?"
/// a: "Invalid `basis`, non-positive `rate`/`par`, or `settlement <= issue`."
/// ```
#[derive(Debug)]
pub struct AccrintmFn;
/// [formualizer-docgen:schema:start]
/// Name: ACCRINTM
/// Type: AccrintmFn
/// Min args: 4
/// Max args: variadic
/// Variadic: true
/// Signature: ACCRINTM(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5...: number@scalar)
/// 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}
/// Caps: PURE
/// [formualizer-docgen:schema:end]
impl Function for AccrintmFn {
func_caps!(PURE);
fn name(&self) -> &'static str {
"ACCRINTM"
}
fn min_args(&self) -> usize {
4
}
fn variadic(&self) -> bool {
true
}
fn arg_schema(&self) -> &'static [ArgSchema] {
use std::sync::LazyLock;
static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
vec![
ArgSchema::number_lenient_scalar(), // issue
ArgSchema::number_lenient_scalar(), // settlement
ArgSchema::number_lenient_scalar(), // rate
ArgSchema::number_lenient_scalar(), // par
ArgSchema::number_lenient_scalar(), // basis (optional)
]
});
&SCHEMA[..]
}
fn eval<'a, 'b, 'c>(
&self,
args: &'c [ArgumentHandle<'a, 'b>],
_ctx: &dyn FunctionContext<'b>,
) -> Result<CalcValue<'b>, ExcelError> {
// Check minimum required arguments
if args.len() < 4 {
return Ok(CalcValue::Scalar(LiteralValue::Error(
ExcelError::new_value(),
)));
}
let issue_serial = coerce_num(&args[0])?;
let settlement_serial = coerce_num(&args[1])?;
let rate = coerce_num(&args[2])?;
let par = coerce_num(&args[3])?;
let basis_int = if args.len() > 4 {
coerce_num(&args[4])?.trunc() as i32
} else {
0
};
// Validate inputs
if rate <= 0.0 || par <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let basis = DayCountBasis::from_int(basis_int)?;
let issue = serial_to_date(issue_serial)?;
let settlement = serial_to_date(settlement_serial)?;
// settlement must be after issue
if settlement <= issue {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
// ACCRINTM = par * rate * year_fraction(issue, settlement)
let yf = year_fraction(&issue, &settlement, basis);
let accrued_interest = par * rate * yf;
Ok(CalcValue::Scalar(LiteralValue::Number(accrued_interest)))
}
}
/// Returns clean price per 100 face value for a coupon-paying security.
///
/// `PRICE` discounts remaining coupons and redemption to settlement and subtracts accrued
/// coupon interest according to the chosen day-count basis.
///
/// # Remarks
/// - Date inputs are spreadsheet serial dates and must satisfy `maturity > settlement`.
/// - `rate` (coupon) and `yld` (yield) are annual decimal rates; `redemption` is amount paid per 100 face value at maturity.
/// - `frequency` must be `1` (annual), `2` (semiannual), or `4` (quarterly).
/// - `basis` codes: `0=US(NASD)30/360`, `1=Actual/Actual`, `2=Actual/360`, `3=Actual/365`, `4=European30/360`.
/// - Return value is quoted per 100 face value; positive inputs usually produce a positive price.
/// - Coupon schedule is derived by stepping backward from `maturity`, with end-of-month adjustment behavior in month arithmetic.
///
/// # Examples
///
/// ```yaml,sandbox
/// title: "Single remaining coupon period"
/// formula: "=PRICE(DATE(2024,4,1), DATE(2024,7,1), 0.06, 0.05, 100, 2, 0)"
/// expected: 100.2283950617284
/// ```
///
/// ```yaml,sandbox
/// title: "Par bond when coupon rate equals yield"
/// formula: "=PRICE(DATE(2024,3,1), DATE(2026,3,1), 0.05, 0.05, 100, 2, 0)"
/// expected: 100
/// ```
/// ```yaml,docs
/// related:
/// - YIELD
/// - ACCRINT
/// - ACCRINTM
/// faq:
/// - q: "Why is `PRICE` quoted per 100 even if my bond face value differs?"
/// a: "This implementation follows Excel quoting conventions and returns clean price per 100 face value."
/// - q: "Which domain checks return `#NUM!`?"
/// a: "`maturity <= settlement`, invalid `basis`, unsupported `frequency`, negative `rate`/`yld`, or non-positive `redemption`."
/// ```
#[derive(Debug)]
pub struct PriceFn;
/// [formualizer-docgen:schema:start]
/// Name: PRICE
/// Type: PriceFn
/// Min args: 6
/// Max args: variadic
/// Variadic: true
/// Signature: PRICE(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5: number@scalar, arg6: number@scalar, arg7...: number@scalar)
/// 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}
/// Caps: PURE
/// [formualizer-docgen:schema:end]
impl Function for PriceFn {
func_caps!(PURE);
fn name(&self) -> &'static str {
"PRICE"
}
fn min_args(&self) -> usize {
6
}
fn variadic(&self) -> bool {
true
}
fn arg_schema(&self) -> &'static [ArgSchema] {
use std::sync::LazyLock;
static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
vec![
ArgSchema::number_lenient_scalar(), // settlement
ArgSchema::number_lenient_scalar(), // maturity
ArgSchema::number_lenient_scalar(), // rate (coupon rate)
ArgSchema::number_lenient_scalar(), // yld (yield)
ArgSchema::number_lenient_scalar(), // redemption
ArgSchema::number_lenient_scalar(), // frequency
ArgSchema::number_lenient_scalar(), // basis (optional)
]
});
&SCHEMA[..]
}
fn eval<'a, 'b, 'c>(
&self,
args: &'c [ArgumentHandle<'a, 'b>],
_ctx: &dyn FunctionContext<'b>,
) -> Result<CalcValue<'b>, ExcelError> {
// Check minimum required arguments
if args.len() < 6 {
return Ok(CalcValue::Scalar(LiteralValue::Error(
ExcelError::new_value(),
)));
}
let settlement_serial = coerce_num(&args[0])?;
let maturity_serial = coerce_num(&args[1])?;
let rate = coerce_num(&args[2])?;
let yld = coerce_num(&args[3])?;
let redemption = coerce_num(&args[4])?;
let frequency = coerce_num(&args[5])?.trunc() as i32;
let basis_int = if args.len() > 6 {
coerce_num(&args[6])?.trunc() as i32
} else {
0
};
// Validate inputs
if rate < 0.0 || yld < 0.0 || redemption <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
if frequency != 1 && frequency != 2 && frequency != 4 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let basis = DayCountBasis::from_int(basis_int)?;
let settlement = serial_to_date(settlement_serial)?;
let maturity = serial_to_date(maturity_serial)?;
// maturity must be after settlement
if maturity <= settlement {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let price = calculate_price(
&settlement,
&maturity,
rate,
yld,
redemption,
frequency,
basis,
);
Ok(CalcValue::Scalar(LiteralValue::Number(price)))
}
}
/// Calculate bond price using standard bond pricing formula
fn calculate_price(
settlement: &NaiveDate,
maturity: &NaiveDate,
rate: f64,
yld: f64,
redemption: f64,
frequency: i32,
basis: DayCountBasis,
) -> f64 {
let n = coupons_remaining(settlement, maturity, frequency);
let coupon = 100.0 * rate / frequency as f64;
// Find previous and next coupon dates
let next_coupon = coupon_date_after(settlement, maturity, frequency);
let prev_coupon = coupon_date_before(settlement, maturity, frequency);
// Calculate fraction of period from settlement to next coupon
let days_to_next = days_between(settlement, &next_coupon, basis) as f64;
let days_in_period = days_between(&prev_coupon, &next_coupon, basis) as f64;
let dsn = if days_in_period > 0.0 {
days_to_next / days_in_period
} else {
0.0
};
let yld_per_period = yld / frequency as f64;
if n == 1 {
// Short first period (single coupon remaining)
// Price = (redemption + coupon) / (1 + dsn * yld_per_period) - (1 - dsn) * coupon
(redemption + coupon) / (1.0 + dsn * yld_per_period) - (1.0 - dsn) * coupon
} else {
// Multiple coupons remaining
// Price = sum of discounted coupons + discounted redemption - accrued interest
let discount_factor = 1.0 + yld_per_period;
// Discount factor for first coupon (fractional period)
let first_discount = discount_factor.powf(dsn);
// Present value of coupon payments
let mut pv_coupons = 0.0;
for k in 0..n {
let discount = first_discount * discount_factor.powi(k);
pv_coupons += coupon / discount;
}
// Present value of redemption
let pv_redemption = redemption / (first_discount * discount_factor.powi(n - 1));
// Accrued interest (negative because we subtract it)
let accrued = (1.0 - dsn) * coupon;
pv_coupons + pv_redemption - accrued
}
}
/// Returns annual yield for a coupon-paying security from its market price.
///
/// `YIELD` solves for the annual rate that makes `PRICE(...)` match the input `pr`.
///
/// # Remarks
/// - Date inputs are spreadsheet serial dates and must satisfy `maturity > settlement`.
/// - `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.
/// - `frequency` must be `1` (annual), `2` (semiannual), or `4` (quarterly).
/// - `basis` codes: `0=US(NASD)30/360`, `1=Actual/Actual`, `2=Actual/360`, `3=Actual/365`, `4=European30/360`.
/// - Result is an annualized decimal yield (for example, `0.05` means 5%).
/// - This implementation uses Newton-Raphson iteration; if it cannot converge, it returns `#NUM!`.
///
/// # Examples
///
/// ```yaml,sandbox
/// title: "Par price implies coupon-rate yield"
/// formula: "=YIELD(DATE(2024,3,1), DATE(2026,3,1), 0.05, 100, 100, 2, 0)"
/// expected: 0.05
/// ```
///
/// ```yaml,sandbox
/// title: "Yield recovered from a discounted price"
/// formula: "=YIELD(DATE(2024,2,15), DATE(2027,2,15), 0.05, 97.2914042780609, 100, 2, 0)"
/// expected: 0.06
/// ```
/// ```yaml,docs
/// related:
/// - PRICE
/// - ACCRINT
/// - ACCRINTM
/// faq:
/// - q: "What does the returned `YIELD` represent?"
/// a: "It is an annualized decimal yield (for example, `0.06` means 6% per year)."
/// - q: "When does `YIELD` return `#NUM!` besides invalid inputs?"
/// a: "The Newton-Raphson solve can fail to converge or hit an unstable derivative; in those cases it returns `#NUM!`."
/// ```
#[derive(Debug)]
pub struct YieldFn;
/// [formualizer-docgen:schema:start]
/// Name: YIELD
/// Type: YieldFn
/// Min args: 6
/// Max args: variadic
/// Variadic: true
/// Signature: YIELD(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar, arg4: number@scalar, arg5: number@scalar, arg6: number@scalar, arg7...: number@scalar)
/// 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}
/// Caps: PURE
/// [formualizer-docgen:schema:end]
impl Function for YieldFn {
func_caps!(PURE);
fn name(&self) -> &'static str {
"YIELD"
}
fn min_args(&self) -> usize {
6
}
fn variadic(&self) -> bool {
true
}
fn arg_schema(&self) -> &'static [ArgSchema] {
use std::sync::LazyLock;
static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
vec![
ArgSchema::number_lenient_scalar(), // settlement
ArgSchema::number_lenient_scalar(), // maturity
ArgSchema::number_lenient_scalar(), // rate (coupon rate)
ArgSchema::number_lenient_scalar(), // pr (price)
ArgSchema::number_lenient_scalar(), // redemption
ArgSchema::number_lenient_scalar(), // frequency
ArgSchema::number_lenient_scalar(), // basis (optional)
]
});
&SCHEMA[..]
}
fn eval<'a, 'b, 'c>(
&self,
args: &'c [ArgumentHandle<'a, 'b>],
_ctx: &dyn FunctionContext<'b>,
) -> Result<CalcValue<'b>, ExcelError> {
// Check minimum required arguments
if args.len() < 6 {
return Ok(CalcValue::Scalar(LiteralValue::Error(
ExcelError::new_value(),
)));
}
let settlement_serial = coerce_num(&args[0])?;
let maturity_serial = coerce_num(&args[1])?;
let rate = coerce_num(&args[2])?;
let pr = coerce_num(&args[3])?;
let redemption = coerce_num(&args[4])?;
let frequency = coerce_num(&args[5])?.trunc() as i32;
let basis_int = if args.len() > 6 {
coerce_num(&args[6])?.trunc() as i32
} else {
0
};
// Validate inputs
if rate < 0.0 || pr <= 0.0 || redemption <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
if frequency != 1 && frequency != 2 && frequency != 4 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let basis = DayCountBasis::from_int(basis_int)?;
let settlement = serial_to_date(settlement_serial)?;
let maturity = serial_to_date(maturity_serial)?;
// maturity must be after settlement
if maturity <= settlement {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
// Use Newton-Raphson to find yield where price = target price
let yld = calculate_yield(
&settlement,
&maturity,
rate,
pr,
redemption,
frequency,
basis,
);
match yld {
Some(y) => Ok(CalcValue::Scalar(LiteralValue::Number(y))),
None => Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
)),
}
}
}
/// Calculate yield using Newton-Raphson iteration
fn calculate_yield(
settlement: &NaiveDate,
maturity: &NaiveDate,
rate: f64,
target_price: f64,
redemption: f64,
frequency: i32,
basis: DayCountBasis,
) -> Option<f64> {
const MAX_ITER: i32 = 100;
const EPSILON: f64 = 1e-10;
// Initial guess based on coupon rate
let mut yld = rate;
if yld == 0.0 {
yld = 0.05; // Default guess if rate is 0
}
for _ in 0..MAX_ITER {
let price = calculate_price(
settlement, maturity, rate, yld, redemption, frequency, basis,
);
let diff = price - target_price;
if diff.abs() < EPSILON {
return Some(yld);
}
// Calculate derivative numerically
let delta = 0.0001;
let price_up = calculate_price(
settlement,
maturity,
rate,
yld + delta,
redemption,
frequency,
basis,
);
let derivative = (price_up - price) / delta;
if derivative.abs() < EPSILON {
return None;
}
let new_yld = yld - diff / derivative;
// Prevent yield from going too negative
if new_yld < -0.99 {
yld = -0.99;
} else {
yld = new_yld;
}
// Prevent yield from going too high
if yld > 10.0 {
yld = 10.0;
}
}
// If close enough after max iterations, return the result
let final_price = calculate_price(
settlement, maturity, rate, yld, redemption, frequency, basis,
);
if (final_price - target_price).abs() < 0.01 {
Some(yld)
} else {
None
}
}
/// Returns bond-equivalent yield for a US Treasury bill.
///
/// `TBILLEQ` converts a T-bill discount rate into an annualized bond-equivalent yield
/// so it can be compared with coupon-bearing securities.
///
/// # Remarks
/// - Date inputs are spreadsheet serial dates; `maturity` must be after `settlement`.
/// - The T-bill must mature within one year of settlement (DSM <= 365).
/// - `discount` is the T-bill discount rate as a decimal (e.g. `0.09` for 9%).
/// - For bills with DSM <= 182: `yield = 365 * discount / (360 - discount * DSM)`.
/// - For bills with DSM > 182 a quadratic coupon-equivalent formula is used.
/// - Returns `#NUM!` for invalid dates, non-positive discount, or DSM out of range.
///
/// # Examples
/// ```excel
/// =TBILLEQ(DATE(2024,1,1), DATE(2024,4,1), 0.038)
/// ```
///
/// ```yaml,sandbox
/// title: "Short bill bond-equivalent yield"
/// formula: '=TBILLEQ(DATE(2024,1,1), DATE(2024,4,1), 0.038)'
/// expected: 0.03890144779577161
/// ```
///
/// ```yaml,docs
/// related:
/// - TBILLPRICE
/// - TBILLYIELD
/// - YIELD
/// faq:
/// - q: "Why does TBILLEQ differ from the discount rate?"
/// a: "TBILLEQ annualizes the bill using a bond-equivalent convention so it can be compared with coupon-bearing yields."
/// ```
#[derive(Debug)]
pub struct TbilleqFn;
/// [formualizer-docgen:schema:start]
/// Name: TBILLEQ
/// Type: TbilleqFn
/// Min args: 3
/// Max args: 3
/// Variadic: false
/// Signature: TBILLEQ(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar)
/// 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}
/// Caps: PURE
/// [formualizer-docgen:schema:end]
impl Function for TbilleqFn {
func_caps!(PURE);
fn name(&self) -> &'static str {
"TBILLEQ"
}
fn min_args(&self) -> usize {
3
}
fn arg_schema(&self) -> &'static [ArgSchema] {
use std::sync::LazyLock;
static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
vec![
ArgSchema::number_lenient_scalar(), // settlement
ArgSchema::number_lenient_scalar(), // maturity
ArgSchema::number_lenient_scalar(), // discount
]
});
&SCHEMA[..]
}
fn eval<'a, 'b, 'c>(
&self,
args: &'c [ArgumentHandle<'a, 'b>],
_ctx: &dyn FunctionContext<'b>,
) -> Result<CalcValue<'b>, ExcelError> {
let settlement_serial = coerce_num(&args[0])?;
let maturity_serial = coerce_num(&args[1])?;
let discount = coerce_num(&args[2])?;
if discount <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let settlement = serial_to_date(settlement_serial)?;
let maturity = serial_to_date(maturity_serial)?;
if maturity <= settlement {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let dsm = (maturity - settlement).num_days() as f64;
if dsm > 365.0 || dsm <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let result = if dsm <= 182.0 {
365.0 * discount / (360.0 - discount * dsm)
} else {
// Coupon-equivalent yield for long-dated T-bills (> 182 days)
// Microsoft formula: (-2a + 2*sqrt(a^2 - (2a-1)*(1 - 1/p))) / (2a-1)
// where a = DSM/365 and p = price as fraction of par
let price_frac = 1.0 - discount * dsm / 360.0;
let a = dsm / 365.0;
let inner = a * a - (2.0 * a - 1.0) * (1.0 - 1.0 / price_frac);
if inner < 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
(-2.0 * a + 2.0 * inner.sqrt()) / (2.0 * a - 1.0)
};
Ok(CalcValue::Scalar(LiteralValue::Number(result)))
}
}
/// Returns price per $100 face value for a US Treasury bill.
///
/// `TBILLPRICE` computes the dollar price from a discount rate using
/// `price = 100 * (1 - discount * DSM / 360)`.
///
/// # Remarks
/// - Date inputs are spreadsheet serial dates; `maturity` must be after `settlement`.
/// - The T-bill must mature within one year of settlement (DSM <= 365).
/// - `discount` is a decimal discount rate; must be positive.
/// - Returns `#NUM!` for invalid dates, non-positive discount, or DSM out of range.
///
/// # Examples
/// ```excel
/// =TBILLPRICE(DATE(2024,1,1), DATE(2024,4,1), 0.038)
/// ```
///
/// ```yaml,sandbox
/// title: "Price a 91-day T-bill"
/// formula: '=TBILLPRICE(DATE(2024,1,1), DATE(2024,4,1), 0.038)'
/// expected: 99.03944444444444
/// ```
///
/// ```yaml,docs
/// related:
/// - TBILLEQ
/// - TBILLYIELD
/// - PRICE
/// faq:
/// - q: "What does TBILLPRICE quote?"
/// a: "The result is the dollar price per 100 of face value, following Excel's Treasury-bill convention."
/// ```
#[derive(Debug)]
pub struct TbillpriceFn;
/// [formualizer-docgen:schema:start]
/// Name: TBILLPRICE
/// Type: TbillpriceFn
/// Min args: 3
/// Max args: 3
/// Variadic: false
/// Signature: TBILLPRICE(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar)
/// 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}
/// Caps: PURE
/// [formualizer-docgen:schema:end]
impl Function for TbillpriceFn {
func_caps!(PURE);
fn name(&self) -> &'static str {
"TBILLPRICE"
}
fn min_args(&self) -> usize {
3
}
fn arg_schema(&self) -> &'static [ArgSchema] {
use std::sync::LazyLock;
static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
vec![
ArgSchema::number_lenient_scalar(), // settlement
ArgSchema::number_lenient_scalar(), // maturity
ArgSchema::number_lenient_scalar(), // discount
]
});
&SCHEMA[..]
}
fn eval<'a, 'b, 'c>(
&self,
args: &'c [ArgumentHandle<'a, 'b>],
_ctx: &dyn FunctionContext<'b>,
) -> Result<CalcValue<'b>, ExcelError> {
let settlement_serial = coerce_num(&args[0])?;
let maturity_serial = coerce_num(&args[1])?;
let discount = coerce_num(&args[2])?;
if discount <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let settlement = serial_to_date(settlement_serial)?;
let maturity = serial_to_date(maturity_serial)?;
if maturity <= settlement {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let dsm = (maturity - settlement).num_days() as f64;
if dsm > 365.0 || dsm <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let price = 100.0 * (1.0 - discount * dsm / 360.0);
Ok(CalcValue::Scalar(LiteralValue::Number(price)))
}
}
/// Returns the yield for a US Treasury bill.
///
/// `TBILLYIELD` computes the discount-rate yield from a T-bill's price using
/// `yield = (100 - price) / price * (360 / DSM)`.
///
/// # Remarks
/// - Date inputs are spreadsheet serial dates; `maturity` must be after `settlement`.
/// - The T-bill must mature within one year of settlement (DSM <= 365).
/// - `price` is the dollar price per $100 face value; must be positive.
/// - Returns `#NUM!` for invalid dates, non-positive price, or DSM out of range.
///
/// # Examples
/// ```excel
/// =TBILLYIELD(DATE(2024,1,1), DATE(2024,4,1), 98.5)
/// ```
///
/// ```yaml,sandbox
/// title: "Yield from quoted T-bill price"
/// formula: '=TBILLYIELD(DATE(2024,1,1), DATE(2024,4,1), 98.5)'
/// expected: 0.060244324203715074
/// ```
///
/// ```yaml,docs
/// related:
/// - TBILLPRICE
/// - TBILLEQ
/// - YIELD
/// faq:
/// - q: "Is TBILLYIELD the same as bond-equivalent yield?"
/// a: "No. TBILLYIELD gives the bill's discount-rate yield, while TBILLEQ converts that pricing into a bond-equivalent yield."
/// ```
#[derive(Debug)]
pub struct TbillyieldFn;
/// [formualizer-docgen:schema:start]
/// Name: TBILLYIELD
/// Type: TbillyieldFn
/// Min args: 3
/// Max args: 3
/// Variadic: false
/// Signature: TBILLYIELD(arg1: number@scalar, arg2: number@scalar, arg3: number@scalar)
/// 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}
/// Caps: PURE
/// [formualizer-docgen:schema:end]
impl Function for TbillyieldFn {
func_caps!(PURE);
fn name(&self) -> &'static str {
"TBILLYIELD"
}
fn min_args(&self) -> usize {
3
}
fn arg_schema(&self) -> &'static [ArgSchema] {
use std::sync::LazyLock;
static SCHEMA: LazyLock<Vec<ArgSchema>> = LazyLock::new(|| {
vec![
ArgSchema::number_lenient_scalar(), // settlement
ArgSchema::number_lenient_scalar(), // maturity
ArgSchema::number_lenient_scalar(), // price
]
});
&SCHEMA[..]
}
fn eval<'a, 'b, 'c>(
&self,
args: &'c [ArgumentHandle<'a, 'b>],
_ctx: &dyn FunctionContext<'b>,
) -> Result<CalcValue<'b>, ExcelError> {
let settlement_serial = coerce_num(&args[0])?;
let maturity_serial = coerce_num(&args[1])?;
let price = coerce_num(&args[2])?;
if price <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let settlement = serial_to_date(settlement_serial)?;
let maturity = serial_to_date(maturity_serial)?;
if maturity <= settlement {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let dsm = (maturity - settlement).num_days() as f64;
if dsm > 365.0 || dsm <= 0.0 {
return Ok(CalcValue::Scalar(
LiteralValue::Error(ExcelError::new_num()),
));
}
let yld = (100.0 - price) / price * (360.0 / dsm);
Ok(CalcValue::Scalar(LiteralValue::Number(yld)))
}
}
pub fn register_builtins() {
use std::sync::Arc;
crate::function_registry::register_function(Arc::new(AccrintFn));
crate::function_registry::register_function(Arc::new(AccrintmFn));
crate::function_registry::register_function(Arc::new(PriceFn));
crate::function_registry::register_function(Arc::new(YieldFn));
crate::function_registry::register_function(Arc::new(TbilleqFn));
crate::function_registry::register_function(Arc::new(TbillpriceFn));
crate::function_registry::register_function(Arc::new(TbillyieldFn));
}
#[cfg(test)]
mod tests {
use super::*;
#[test]
fn year_fraction_actual_actual_prorates_cross_year_ranges() {
let start = NaiveDate::from_ymd_opt(2023, 12, 1).unwrap();
let end = NaiveDate::from_ymd_opt(2024, 3, 1).unwrap();
let expected = 31.0 / 365.0 + 60.0 / 366.0;
let actual = year_fraction(&start, &end, DayCountBasis::ActualActual);
assert!(
(actual - expected).abs() < 1e-12,
"expected {expected}, got {actual}"
);
}
#[test]
fn year_fraction_actual_actual_is_sign_symmetric() {
let start = NaiveDate::from_ymd_opt(2023, 12, 1).unwrap();
let end = NaiveDate::from_ymd_opt(2024, 3, 1).unwrap();
let forward = year_fraction(&start, &end, DayCountBasis::ActualActual);
let backward = year_fraction(&end, &start, DayCountBasis::ActualActual);
assert!((forward + backward).abs() < 1e-12);
}
}