Excel Formulas for Private Equity: EFFECT
What is EFFECT Function in Excel?
The EFFECT function in Excel is a financial function that returns the effective annual interest rate, using the nominal interest rate (APR) and the number of compounding periods per year. The effective annual interest rate is the actual interest rate earned due to compounding.
=EFFECT (nominal_rate, npery)
Nominal rate – The nominal interest rate (APR)
Npery – The number of compounding periods per year
Why is the EFFECT Formula Important?
The EFFECT function is often used as a contributing factor in real estate financial modeling to make informed decisions regarding loans. This function calculates the actual interest rate on a financial product that may differ from the stated rate due to compounding.
It is important to note that the arguments within the function have to be numeric values with the nominal rate between 0 and 1, and the Npery value as an integer.
How is it Used in Real Estate?
We will use this Corporate Model from Top Shelf Models to demonstrate how we have used the EFFECT function in real estate financial modeling.
In this example, the formula in cell C57 in the Fund Assumptions tab is doing the following:
The formula is saying that Excel should calculate the effective rate of interest by using the nominal rate of 7% in cell C55 by compounding it by 2 periods as assumed in cell C56 to get the effective rate of 7.12%
We have used the function to determine the actual rate in the waterfall structure to make an informed decision regarding the fund.
