NOTE: If you only need to convert one cell (or a few rather than many) formulas, you can double-click in a cell containing a formula and press F9 to convert that formula to a static value. If the result of a part of a formula will not change, but the results from rest of the formula will vary, you can convert part of the formula to a static. An argument in VALUE Formula. Text – Is a required argument. That specifies numeric strings such as numbers, dates, time, etc., which have not been recognized by excel as numeric values and hence stored as strings. How to Use Excel VALUE Formula in Excel? Excel VALUE Formula is very simple and easy. CONVERT(number,fromunit,tounit) Number is the value in fromunits to convert. Fromunit is the units for number. Tounit is the units for the result. CONVERT accepts the following text values (in quotation marks) for fromunit and tounit. To easily us the formula to values, you can simply utilize the keyboard. This is done by simply selecting the cell that you want to replace its formula with value. Then we have to copy the cell by pressing Ctrl + C. The last thing we want to do in order to have the formula replaced by value is to paste it as a value.
- Converting Formulas To Values Using Excel
- Converting Formulas To Values Using Excel Spreadsheet
- Converting Formulas To Values Using Excel Using
- Converting Formulas To Values Using Excel Formula
The present value calculations on this page are applied to investments for which interest is compounded in each period of the investment.
However if you are supplied with a stated annual interest rate, and told that the interest is compounded monthly, you will need to convert the annual interest rate to a monthly interest rate and the number of periods into months:
| monthly interest rate | = | annual interest rate / 12 |
| number of months | = | number of years * 12 |
A similar conversion is required if interest is paid quarterly, semi-annually, etc.
For an example of this, see the section on How To Calculate Present Value When Interest is Compounded Monthly
Present Value Index:
| Present Value of a Single Cash Flow |
| Present Value of a Series of Cash Flows |
| Present Value of a Perpetuity |
Present Value of a Single Cash Flow
If you want to calculate the present value of a single investment that earns a fixed interest rate, compounded over a specified number of periods, the formula for this is:
where,
- fv is the future value of the investment;
- rate is the interest rate per period (as a decimal or a percentage);
- nper is the number of periods over which the investment is made.
| A | B | |
|---|---|---|
| 1 | Future Value: | 15000 |
| 2 | Annual Interest Rate: | 4% |
| 3 | Number of Years: | 5 |
| 4 | Present Value: | =15000/(1+4%)^5 |
For example, if you want a future value of $15,000 in 5 years' time from an investment which earns an annual interest rate of 4%, the present value of this investment (i.e. the amount you will need to invest) can be calculated by typing the following formula into any Excel cell:
which gives the result 12328.9066.
I.e. the present value of the investment (rounded to 2 decimal places) is $12,328.91.
As with all Excel formulas, instead of typing the numbers directly into the present value formula, you can use references to cells containing values. Therefore, the present value formula in cell B4 of the above spreadsheet could be entered as:
which returns the same result.
Using the Excel PV Function to Calculate the Present Value of a Single Cash Flow
Instead of using the above formula, the present value of a single cash flow can be calculated using the built-in Excel PV function (which is generally used for a series of cash flows).
The syntax of the PV function is:
where,
Converting Formulas To Values Using Excel
- rate is the interest rate per period (as a decimal or a percentage);
- nper is the number of periods over which the investment is made;
- [pmt] is the regular payment per period (if omitted, this is set to the default value 0);
- [fv] is the future value of the investment, at the end of nper payments (if omitted, this is set to the default value 0);
- [type] specifies whether the payment is made at the start or the end of the period.
This can have the value 0 or 1, meaning:
0 - the payment is made at the end of the period (as for an ordinary annuity);
1 - the payment is made at the start of the period (as for an annuity due).If omitted, the [type] argument is set to the default value 0.
Note that, in line with the general cash flow sign convention, the PV function treats negative values as outflows and positive values as inflows.
| A | B | |
|---|---|---|
| 1 | Future Value: | 15000 |
| 2 | Annual Interest Rate: | 4% |
| 3 | Number of Years: | 5 |
| 4 | Present Value: | =PV( 4%, 5, 0, 15000 ) |
For example, the above spreadsheet on the right shows the Excel PV function used to calculate the present value of an investment that earns an annual interest rate of 4% and has a future value of $15,000 after 5 years.
As shown in cell B4 of the spreadsheet, the PV function to calculate this is:
which gives the result -$12,328.91.
Note that in the above PV function:
- The [pmt] argument is set to 0, as there are no ongoing payments after the initial investment;
- The returned present value is negative, representing an outgoing payment.
How To Calculate Present Value When Interest is Compounded Monthly
If the interest on your investment is compounded monthly (while being quoted as an annual interest rate), the annual interest rate needs to be converted into a monthly interest rate and the number of years needs to be converted into months.
I.e.
| monthly interest rate | = | annual interest rate / 12 |
| number of months | = | number of years * 12 |
| A | B | |
|---|---|---|
| 1 | Future Value: | 15000 |
| 2 | Annual Interest Rate: | 4% |
| 3 | Number of Years: | 5 |
| 4 | Present Value: | =PV( 4%/12, 5*12, 0, 15000 ) |
Therefore, if an investment has a stated annual interest rate of 4% (compounded monthly), and returns $15,000 after 5 years, the present value of the investment can be calculated as follows:
which returns the result -$12,285.05.
(Note that, once again, the value returned from the PV function is negative, representing an outgoing payment).
Present Value of a Series of Cash Flows (An Annuity)
If you want to calculate the present value of an annuity (a series of periodic constant cash flows that earn a fixed interest rate over a specified number of periods), this can be done using the Excel PV function.
The syntax of the PV function is:
where,
- rate is the interest rate per period (as a decimal or a percentage);
- nper is the number of periods over which the investment is made;
- [pmt] is the regular payment per period (if omitted, this is set to the default value 0);
- [fv] is the future value of the investment, at the end of nper payments (if omitted, this is set to the default value 0);
- [type] specifies whether the payment is made at the start or the end of the period.
This can have the value 0 or 1, meaning:
0 - the payment is made at the end of the period (as for an ordinary annuity);
1 - the payment is made at the start of the period (as for an annuity due).If omitted, the [type] argument is set to the default value 0.
Note that, in line with the general cash flow sign convention, the PV function treats negative values as outflows and positive values as inflows.
| A | B | |
|---|---|---|
| 1 | Annual Interest Rate: | 4% |
| 2 | Number of Years: | 5 |
| 3 | Annual Payment: | 500 |
| 4 | Present Value: | =PV( 4%, 5, 500 ) |
For example, to calculate the present value of an ordinary annuity that has an annual interest rate of 4% and returns payments of $500 per year for 5 years, type the following formula into any Excel cell:
which gives the result -$2,225.91.
Note that in the above PV function:
- The [fv] argument is omitted, and so takes on the default value 0;
- the [type] argument is omitted, and so takes on the default value 0 (i.e. the calculation assumes that the payment is made at the end of each year);
- The returned present value is negative, representing an outgoing payment.
Again, as with all Excel formulas, instead of typing the numbers directly into the present value formula, you can use references to cells containing values. Therefore, the PV function in cell B4 of the above spreadsheet could be entered as:
which returns the same result.
Present Value of a Perpetuity
A perpetuity is an annuity in which the constant periodic payments continue indefinitely.
The formula to calculate the present value of a perpetuity is:
where,
- pmt is the regular payment per period;
- rate is the interest rate per period (as a decimal or a percentage).
| A | B | |
|---|---|---|
| 1 | Annual Payment: | 30000 |
| 2 | Annual Interest Rate: | 3.5% |
| 3 | Present Value: | =30000/3.5% |
For example, if you have a perpetuity that pays $30,000 per year and has an annual interest rate of 3.5%, the present value of the perpetuity can be calculated by typing the following formula into any Excel cell:
This gives the result 857142.8571.
I.e. the present value of the perpetuity (rounded to 2 decimal places) is $857,142.86.
Return to the ExcelFunctions.net Home Page
When working with Excel, a time comes when we have to use formulas in our calculations. While this is the best and easiest way to arrive at a result, leaving the formula can be dangerous, especially if we want to share the workbook with someone else. Also, if we are to update the sheet on a different day, then it becomes necessary to convert the formula to its actual value. Think of a situation where we use a DATE function. If we leave the function as it is, then we shall have a different answer with each passing day. This post looks at the steps required to replace formula with value.
There are a number of ways through which we can convert formulas to values. Below are some of the most common ways used by Excel users;
Converting Formulas To Values Using Excel Spreadsheet
Using keyboard shortcut to replace formula with values
To easily us the formula to values, you can simply utilize the keyboard. This is done by simply selecting the cell that you want to replace its formula with value. Then we have to copy the cell by pressing Ctrl + C. The last thing we want to do in order to have the formula replaced by value is to paste it as a value. This is done by pressing Alt + ESV
Figure 1: Pasting as values
Converting Formulas To Values Using Excel Using
Replace formula with values by paste special
Paste special is another very essential option we have to convert formulas to values. This option can easily be accessed via the ribbon.
Using this method is equally simple and straightforward. All you need to do is to select the cells whose formulas you want to convert into values. The click on the Home tab, the click clipboard, and then Paste Special.
Figure 2: Access the Paste Special option via ribbon
Alternatively, right-click on the selected cells. Then hover the mouse on the Paste Special to access the other options.
Figure 3: Access the Paste Special option via right-clicking
Conclusion
In either of the above methods, we shall be able to convert the cells with formulas into values easily.
Instant Connection to an Expert through our Excelchat Service
Converting Formulas To Values Using Excel Formula
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.