The PV function is a financial function that returns the present value of an investment. You can use the PV function to get the value in today's dollars of a series of future payments, assuming periodic, constant payments and a constant interest rate. An...Read more
The FV function is a financial function that returns the future value of an investment, given periodic, constant payments with a constant interest rate. The PV function returns the present value of an investment. You can...Read more
CAGR stands for Compound Annual Growth Rate. CAGR is the average rate of return for an investment over a period of time. It is the rate of return required for an investment to grow from the starting balance to the ending balance, assuming profits are reinvested each year, and...Read more
Net Present Value (NPV) is the present value of expected future cash flows minus the initial cost of investment. The NPV function in Excel only calculates the present value of uneven cashflows, so the initial cost must be handled explicitly.
One way to calculate Net Present Value in...Read more
In this example, the goal is to generate a list of the sheet names in an Excel workbook with a formula. Unfortunately, there is no simple way to do this with a formula in Excel. However, it can be done with a two-step approach:
Working from the inside out, the MID function is used to generate an array from text entered in B5 with this snippet:
MID(B5,ROW(INDIRECT("1:"&LEN(B5))),1)
This formula breaks ties with a simple approach: this first tie in a list "wins" and is assigned the higher rank. The first part of the formula uses the RANK function normally:
=RANK(C5,points)
Rank returns a computed rank, which will...Read more
Data validation rules are triggered when a user adds or changes a cell value. When a custom formula returns TRUE, validation passes and the input is accepted. When a formula returns FALSE, validation fails and the input is rejected with a popup message.
In this case, we have previously...Read more
The first thing this formula does is check the date in column B against the start date:
=IF(B4>=start
If the date is not greater than the start date, the formula returns zero. If the date is greater than or equal to the start date, the...Read more
The COMPLEX function returns the string representation of a complex number. For example:
=COMPLEX(4,3) // returns "4+3i"
To use the "j" instead of "i":
=COMPLEX(4,3,"j") // returns "4+3j"...Read more