Excel like a finance pro.
YEARFRAC
DatesThe fraction of a year between two dates — and the basis argument that quietly changes every accrual.
Difficulty
1What is it?
Any interest accrual, pro-rated fee or part-period charge needs to know what share of a year a period represents. YEARFRAC answers that, and its optional basis argument decides which day-count convention it uses — 30/360, actual/actual, actual/360, actual/365. They disagree. The same period priced on actual/360 rather than 30/360 can differ by a percent or more, which surfaces months later as an unexplained variance nobody can trace. The basis isn't a formatting preference; it's a term of the agreement, and it belongs in the formula explicitly rather than left to the default.
2What it looks like
YEARFRAC(start_date, end_date, [basis])- start_date
- Beginning of the period.
- end_date
- End of the period.
- [basis]
- 0 (default) US 30/360 · 1 actual/actual · 2 actual/360 · 3 actual/365 · 4 European 30/360. Default is 0, which is rarely what a loan agreement says.
3When you use it
- Accrue interest for a stub period at the convention the agreement specifies.
- Pro-rate an annual fee, a subscription or a lease across a part year.
- Compute tenure or asset age as a decimal for a depreciation calculation.
4See it in action
Change the inputs — the formula and result update live. Prefer the real thing? Download the Excel file and open it in Excel.
The same period, priced five ways. The default is rarely what the agreement says.
=YEARFRAC(B1, B2, 2) * Principal * Rate0 · US 30/360 (default): 0.2917 yr → $6,563| A | |
|---|---|
| 1 | Result |
| 2 | 0 · US 30/360 (default): 0.2917 yr → $6,563 |
| 3 | 1 · actual/actual: 0.2877 yr → $6,473 |
| 4 | 2 · actual/360: 0.2917 yr → $6,563 |
| 5 | 3 · actual/365: 0.2877 yr → $6,473 |
| 6 | Spread between conventions: $90 |
The lime cell holds the formula — click it (or any cell) to see its contents in the bar above, just like Excel. Edit the blue cells to watch it recompute.
5Common errors
Numbers don't tie to the lender'sThe default basis of 0 (30/360) was used where the agreement says actual/360.Fix: Pass the basis explicitly, every time, and put the convention in a labelled cell so it's visible.
#NUM!basis isn't 0-4.Fix: Only those five exist.
#VALUE!A date is unparseable text.Fix: Convert with DATEVALUE first.
Slightly under a year for a full yearUnder actual/360, 365 days over 360 is more than 1 — under actual/365 it's exactly 1.Fix: Both are correct for their convention. Check which one you meant.
6Better functions & alternatives
- DAYS / DAYS360 — The same conventions expressed as a day count rather than a year fraction.
- DATEDIF — For whole months and years rather than a decimal fraction.
Want YEARFRAC already wired into a model? Wauvel's free tools download as branded, formula-driven Excel.
Learn the moves here — or let Wauvel run them on your numbers.
Meet your AI CFO →One CFO-grade Excel tip a week
A short, practical email for finance operators — functions, shortcuts, and the moves that save an afternoon. Free, unsubscribe anytime.