The YEARFRAC function in Excel calculates the fractional number of years between two dates. This function can be useful when dealing with financial calculations or when you need to determine the amount of time that has elapsed between two dates.

The syntax for the YEARFRAC function is as follows:

Where:

·        start_date is the starting date of the period.

·        end_date is the ending date of the period.

·        basis is an optional argument that specifies the day-count basis to use for the calculation. If this argument is omitted, Excel assumes a basis of 0, which is equivalent to the U.S. (NASD) 30/360 day-count basis.

There are several day-count basis options available, including:

·        0 (NASD) 30/360

·        1 (Actual/actual)

·        2 (Actual/360)

·        3 (Actual/365)

·        4 (European 30/360)

To use the YEARFRAC function, simply enter the function into a cell in your spreadsheet, specifying the start and end dates and, if necessary, the day-count basis. The function will return a decimal value representing the fractional number of years between the two dates.

For example, if you wanted to calculate the fractional number of years between January 1, 2020 and March 4, 2023, using the actual/actual day-count basis, you would use the following formula:

=YEARFRAC("1/1/2020", "3/4/2023", 1)

This would return a value of 3.17486339, indicating that 3.17486339 years have elapsed between the two dates.

How to apply the YEARFRAC function in a project finance model?

When building a project finance model with quarterly periods, the YEARFRAC function can be helpful in calculating the operational expenditures on an actual/actual basis. This is because inputs for operational expenditures are typically given on an annual basis, but a quarterly project finance model requires quarterly calculations.

Assume we have an annual operational expenditure of €100,000 for the year 2024, and we want to distribute it evenly across the four quarters of the year. We can set up a YEARFRAC function for each quarter of the year and then multiply the annual expense by the fractional portion of the respective quarter.

The formula uses the YEARFRAC function to calculate the fraction of the year that falls within the specified quarter, based on the actual number of days in the quarter. To ensure that the calculation includes all the days in the quarter, we subtract one day from the start date in the first argument of the DATE function. This fraction is then multiplied by the annual operational expenditure to arrive at the quarterly operational expenditure.

For example, to calculate the operational expenditure for the first quarter (January 1 - March 31, 2024) using January 1, 2024, as the reference date, we would use the following formula:

=YEARFRAC(DATE(2024,1,1)-1, DATE(2024,3,31), 1) * €100,000

This would return a value of approximately €24,863.39, which represents the operational expenditure for the first quarter of 2024 on an actual/actual basis.

By using the YEARFRAC function in this way, we can ensure that our project finance model accurately reflects the actual expenditures for each quarter on an actual/actual basis, taking into account the actual number of days in each quarter.

Enhance the decision-making process of your organization with a comprehensive financial model dashboard.

If you're looking to invest in renewable energy, you need a comprehensive financial model dashboard that summarizes all the key investment metrics relevant to your decision-making process.

This financial model dashboard for renewable energy investments is designed to help you make informed investment decisions with ease. It includes a detailed breakdown of the project's capital structure, allowing you to understand the financing mix of the project and the risks associated with each layer of the capital stack.

The dashboard also considers critical investment metrics such as IRR and NPV on both a levered and unlevered basis, giving you a complete picture of the project's profitability.

In addition to these essential investment ratios and multiples, the dashboard details the payback periods, CFADS/EV, Revenue/EV, EV/MWp, and EV/MWh. These metrics are crucial in evaluating the financial viability of the project and determining its potential for generating returns over the long term.

The dashboard also features eye-catching charts that provide a visual representation of the project's cash flow generation and cash on cash returns over its entire asset lifetime. This feature will give you valuable insights into the project's cash flow patterns and help you make informed investment decisions.

With all these critical investment metrics and data points in one place, this financial model dashboard is the ultimate tool for renewable energy investment decision-makers.

Article sources

YEARFRAC function - Excel for Microsoft 365