Skip to content
All library documents

Excel YEARFRAC with the 30/360 Day-Count Basis

Article Quant Q&A · Author: David Worenklein
Read the original

Summary

The note explains why two Excel YEARFRAC calculations using the 30/360 basis return the same result when their date pairs are each one calendar day apart. Under the described interpretation, YEARFRAC computes the year fraction from the days between the supplied dates using a 360-day year. Multiplying that fraction by 360 therefore gives one day for either pair, including dates around the end of February.

The response clarifies that the expected value of three requires a three-day interval in the inputs; it illustrates this with a date pair spanning three days. This addresses the specific Excel 2010 question but does not explore all conventions for 30/360 day counts or compare Excel’s handling with other financial software. Users applying day-count rules to bonds or other instruments should confirm the convention required by the instrument, since date adjustment rules can vary across 30/360 variants.

Key ideas

  • Both date pairs in the question are separated by one day.
  • The selected YEARFRAC basis uses a 360-day denominator in the explanation.
  • Multiplying a one-day year fraction by 360 gives one, not three.
  • A three-day result requires dates that span three days under this calculation.
  • Different instruments and systems may apply distinct 30/360 conventions.

Tags

Fixed incomeStatistics
Full text
# excel yearfrac 30u/360 across feb month end


# excel yearfrac 30u/360 across feb month end












Why are both of these formulas returning 1? One of them should be 3.

=YEARFRAC(DATE(2015,2,27),DATE(2015,2,28),0)*360 =YEARFRAC(DATE(2015,2,28),DATE(2015,3,1),0)*360

Based on every description of 30U/360 that I can find, the first formula should return 3. But even if it were the second one, I could deal with that.

I'm using Excel 2010 14.0.7128.5000 fwiw

## Answer by Brad S (score 1)

https://quant.stackexchange.com/a/20907

YearFrac returns the fraction of a year represented by the number of whole days between each date entered. The dates entered are one day apart in both cases.

The number of days are then divided by the assumed number of days in the year. Entering 0 gives the argument for 30/360 which is 30 days in all months and 360 days per year.

Everything before the ,0) is the numerator. The last argument in yearfrac defines the denominator, which you have set to 360 days in a year. Essentially your formula simplifies to 1/360. Multiply that by 360 and it evaluates to 1.

Both cases listed are only separated by 1 day and so the formulae are equivalent and equal to 1, not 3. To return 3 you need to increase the difference between the two dates entered. =YEARFRAC(DATE(2015,2,25),DATE(2015,2,28),0)*360

Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)

This summary was written by Stratmill's research agent from the original; it is not a copy of the source.