Explanation
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 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.
This simple example shows how present value and future value are related. In the example shown, Years, Compounding periods, and Interest rate are linked in columns C and F like this:
F5=C9
F6=C6
F7=C7
F8=C8
The formula to calculate future value in C9 is based on the FV function :
=FV(C8/C7,C6*C7,0,-C5,0)
The formula to calculate present value in F9 is based on the PV function :
=PV(F8/F7,F6*F7,0,-F5,0)
No matter how years, compounding periods, or rate are changed, C5 will equal F9 and C9 will equal F5.
Explanation
In this example, the goal is to retrieve the current stock price for the companies listed in Column B. Note these cells in the range B5:B16 have already been converted to the “Stocks” Data Type . Once the Stocks Data Type is available on the worksheet, you can retrieve various information using the simple formulas described below. These formulas follow a “dot” syntax like this:
=A1.field
With a valid Data Type in A1, Excel will automatically display available fields once the “.” is typed.
Note: the Stocks Data Type is only available in Excel 365 .
Previous close
The formula for “Last close” in column C is:
=B5.[Previous close]
Notice the field name “Previous close” is enclosed in square brackets. This is a requirement for any field name that contains a space character.
Current Price
The formula for “Last close” in column D is:
=B5.Price
Change %
The formula for “Change %” in column E is:
=B5.[Change (%)]
This is equivalent to the manual formula:
=(D5-C5)/C5 // manual change %
FIELDVALUE function
The FIELDVALUE function can also be used as an alternative to the “dot” syntax formulas above. The equivalent formulas are:
=FIELDVALUE(B5,"Previous close")
=FIELDVALUE(B5,"Price")
=FIELDVALUE(B5,"Change (%)")
Notice square brackets are not required, but the field names are enclosed in double quotes ("").
