[Python] MDURATION logic to be implemented in the same way as implemented in Excel

• 
  My requirement is that I want the same output as what we get in excel for the function MDURATION.
  
  
• 
  I have written a class which mimics the same logic as that of MDURATION. However, it is giving result which is somewhat off the actual result of excel. But I want the result to be exactly that of excel.
  

Appreciate if anyone can help provide a solution to this .

Also, if there are any libraries which provide this feature out-of-the-box, please write here so I can take a look.

package com.example.mduration;

import java.math.BigDecimal;
import java.math.MathContext;
import java.math.RoundingMode;
import java.time.LocalDate;
import java.time.temporal.ChronoUnit;

public class BondDurationCalculator1 {

// Define MathContext for high precision and rounding
private static final MathContext mc = new MathContext(15, RoundingMode.HALF_UP);

/**
* Calculates the Modified Duration (MDURATION equivalent in Excel) for a bond.
*
* @param settlement The settlement date of the bond (when the bond is purchased).
* @param maturity The maturity date of the bond (when the bond matures).
* @param coupon The annual coupon rate (as a decimal, e.g., 0.08 for 8%).
* @param yield The bond's annual yield (as a decimal, e.g., 0.09 for 9%).
* @param frequency The number of coupon payments per year (1 = annual, 2 = semiannual, 4 = quarterly).
* @param basis The day count basis (0 = 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360).
* @return The modified duration of the bond.
*/
public static BigDecimal calculateMDuration(LocalDate settlement, LocalDate maturity,
double coupon, double yield, int frequency, int basis) {

// Calculate years to maturity based on the day count basis
BigDecimal yearsToMaturity = calculateYearFraction(settlement, maturity, basis);

// Calculate Macaulay Duration
BigDecimal macaulayDuration = calculateMacaulayDuration(coupon, yield, frequency, yearsToMaturity);

// Modified Duration = Macaulay Duration / (1 + (Yield / Frequency))
BigDecimal one = new BigDecimal(1, mc);
BigDecimal yieldPerFrequency = new BigDecimal(yield, mc).divide(new BigDecimal(frequency, mc), mc);
BigDecimal denominator = one.add(yieldPerFrequency, mc);

return macaulayDuration.divide(denominator, mc);
}

/**
* Calculates the fraction of the year between two dates based on the specified day count basis.
*
* @param settlement The settlement date.
* @param maturity The maturity date.
* @param basis The day count basis.
* @return The year fraction.
*/
public static BigDecimal calculateYearFraction(LocalDate settlement, LocalDate maturity, int basis) {
long daysBetween = ChronoUnit.DAYS.between(settlement, maturity);

switch (basis) {
case 0: // US (NASD) 30/360
case 4: // European 30/360
return calculate30_360(settlement, maturity);

case 1: // Actual/Actual
return new BigDecimal(daysBetween, mc).divide(isLeapYear(maturity.getYear()) ? new BigDecimal(366, mc) : new BigDecimal(365, mc), mc);

case 2: // Actual/360
return new BigDecimal(daysBetween, mc).divide(new BigDecimal(360, mc), mc);

case 3: // Actual/365
return new BigDecimal(daysBetween, mc).divide(new BigDecimal(365, mc), mc);

default:
throw new IllegalArgumentException("Invalid basis value.");
}
}

/**
* Helper function to calculate Macaulay Duration (a helper method).
*
* @param coupon The bond's coupon rate.
* @param yield The bond's yield to maturity.
* @param frequency The number of coupon payments per year.
* @param yearsToMaturity The total years to maturity.
* @return The Macaulay duration.
*/
public static BigDecimal calculateMacaulayDuration(double coupon, double yield, int frequency, BigDecimal yearsToMaturity) {
BigDecimal macaulayDuration = BigDecimal.ZERO;
BigDecimal sumDiscountedCashFlows = BigDecimal.ZERO;

// Loop through each coupon period
for (int i = 1; i <= yearsToMaturity.multiply(new BigDecimal(frequency, mc), mc).intValue(); i++) {
BigDecimal period = new BigDecimal(i, mc).divide(new BigDecimal(frequency, mc), mc);
BigDecimal discountedCashFlow = new BigDecimal(coupon / frequency, mc)
.divide((BigDecimal.ONE.add(new BigDecimal(yield / frequency, mc)).pow(i, mc)), mc);
macaulayDuration = macaulayDuration.add(period.multiply(discountedCashFlow, mc), mc);
sumDiscountedCashFlows = sumDiscountedCashFlows.add(discountedCashFlow, mc);
}

// Add the final principal payment
BigDecimal discountedPrincipal = BigDecimal.ONE.divide(BigDecimal.ONE.add(new BigDecimal(yield / frequency, mc))
.pow(yearsToMaturity.multiply(new BigDecimal(frequency, mc), mc).intValue(), mc), mc);
macaulayDuration = macaulayDuration.add(yearsToMaturity.multiply(discountedPrincipal, mc), mc);
sumDiscountedCashFlows = sumDiscountedCashFlows.add(discountedPrincipal, mc);

return macaulayDuration.divide(sumDiscountedCashFlows, mc);
}

/**
* Helper function to calculate the year fraction using the 30/360 basis.
*/
public static BigDecimal calculate30_360(LocalDate settlement, LocalDate maturity) {
int settlementDay = settlement.getDayOfMonth();
int settlementMonth = settlement.getMonthValue();
int settlementYear = settlement.getYear();

int maturityDay = maturity.getDayOfMonth();
int maturityMonth = maturity.getMonthValue();
int maturityYear = maturity.getYear();

if (settlementDay == 31) settlementDay = 30;
if (maturityDay == 31 && settlementDay == 30) maturityDay = 30;

return new BigDecimal(((maturityYear - settlementYear) * 360.0
+ (maturityMonth - settlementMonth) * 30.0
+ (maturityDay - settlementDay)) / 360.0, mc);
}

/**
* Helper function to check if a year is a leap year.
*/
public static boolean isLeapYear(int year) {
return (year % 4 == 0 && year % 100 != 0) || (year % 400 == 0);
}

public static void main(String[] args) {
// Example bond details (Basis 3: Actual/365)
LocalDate settlement = LocalDate.of(2024, 4, 30);
LocalDate maturity = LocalDate.of(2025, 7, 18);
double coupon = 0.047155; // 4.7155% annual coupon
double yield = 0.066339179; // 6.6339179% annual yield
int frequency = 4; // Quarterly payments
int basis

= 3; // Actual/365 basis

BigDecimal modifiedDuration = calculateMDuration(settlement, maturity, coupon, yield, frequency, basis);

// Print the result to 10 decimal places
System.out.println("Modified Duration: " + modifiedDuration.setScale(10, RoundingMode.HALF_UP));
}
}


result of excel : 1.68... result of program : 1.69...

result of excel : 1.68... result of program : 1.69... I want the result of excel. But I'm getting value that is slightly off.

Continue reading...