If you want to calculate future investment with inflation and nominal interest rates, then there is an essential article on How to calculate future value with inflation in Excel on our website for you to check. That article covers all the necessary introductory information on Inflation, Future Value, Nominal Interest Rate, and Real Rate of Return. However, this article will discuss how to calculate the future investment value with inflation, nominal interest rates and this time we will add a very crucial part – Tax Rate – into our formula to extract the Future Value in Excel.
Download Workbook
You can download the free practice Excel workbook from here.
What is Future Value and How to Calculate It?
Future Value is categorized under the financial functions in Excel. It calculates the future value of an investment based on fixed interest rates.
- Syntax
=FV(rate,nper,pmt,[pv],[type])
- Argument Description
| Argument | Required/ Optional | Description |
|---|---|---|
| rate | Required | The interest rate per period. |
| nper | Required | The total number of payment periods. |
| pmt | Required | The payment is made in each period. It contains principal and interest but no taxes or extra fees. Must be entered as a negative number. If omitted, argument pv must be included in the formula. |
| [pv] | Optional | The present value. Must be entered as a negative number. If omitted, it is assumed to be zero (0) and argument pmt must be included in the formula. |
| [type] | Optional | Indicates when payments are due with the number 0 or 1.
|
- Return Value
Calculated Future Value.
2 Examples to Calculate Future Investment Value with Inflation, Taxation and Interest Rates in Excel
This section will show you how to calculate Future Investment Value with Initial Investment and without Initial Investment along with the Inflation, Taxation and Interest Rates in Excel.
1. Compute Future Value with Inflation, Tax Rate, Nominal Interest and Initial Investment in Excel
Let’s consider a scenario where,
- The Initial Investment: $50,000 = Present Value (pv)
- Payment per Month: $2,500 = Payment per period (pmt)
- Nominal Interest Rate (yearly): 10%
- Inflation Rate (yearly): 4%
- Tax Rate: 12%
- Payment Frequency (yearly): 12
- Total Time (in years): 10
- Number of Periods (nper) can be calculated from above two input data.
- nper = Payment Frequency*Total Time = 12*10 = 120
- Payment is due at the beginning of the period = type 1
We have all the arguments to calculate the Future Value except the argument rate. Now look closely, there are three types of rates available in this scenario-Â Nominal Interest Rate, Inflation Rate and Tax Rate. So to get the actual rate to achieve the future value, we need to do some basic mathematical calculations here.
To calculate the Total Interest Rate = (Nominal Interest Rate*(1 – Tax Rate)) – Inflation Rate
So according to our data, Total Interest Rate = (10%*(1-12%)) – 4%
And to get the Rate over per given period, we need to divide the result with the total Payment Frequency in a year. Therefore,
Total Interest Rate = ((10%*(1-12%)) – 4%)/12 = 0.4%
And according to our Excel sheet, the formula is,
=((C5*(1-C7))-C6)/C8Where,
- C5 = Nominal Interest Rate
- C6 = Inflation Rate
- C7 = Tax Rate
- C8 = Payment Frequency
Now we have found all of the arguments to calculate the Future Value (fv).
So to calculate the Future Value for our dataset the formula will be,
=FV(C9,C11,C12,C13,C14)Where,
- C9 = Total Interest, rate
- C11 = Number of Periods, nper
- C12 = Payment per Period, pmt
- C13 = Present Value, pv
- C14 = Due Payment, type
As shown in the above picture, after following this formula you can get the Future Value with Inflation, Taxation and Nominal Rates in Excel.
Read More: How to Calculate Future Value in Excel with Different Payments
Similar Readings
- How to Calculate Interest Rate from EMI in Excel (with Easy Steps)
- Calculate Monthly Interest Rate in Excel (3 Simple Methods)
- How to Find Interest Rate in Future Value Annuity (2 Examples)
- Calculate Future Value When CAGR Is Known in Excel (2 Methods)
- How to Calculate Future Value of Growing Annuity in Excel
2. Measure Upcoming Investment Value with Inflation, Tax Rate, Nominal Interest and No Initial Investment Value
If you don’t want to include any initial investment to calculate the future value, then you still can get the job done with the procedure we just showed you in the above section. You can leave the Present Value(pv) argument empty but don’t forget to add Payment per Period (pmt) into your calculation.
So with the same dataset above, we calculated the Future Value without any initial investment this time and the formula for that is,
=FV(C9,C11,C12,C13)Where,
- C9 = Total Interest, rate
- C11 = Number of Periods, nper
- C12 = Payment per Period, pmt
- C13Â = Due Payment, type
As shown in the above picture, after implementing this formula you can get the Future Value of zero investment with Inflation, Tax and Nominal Rates in Excel.
Read More: Nominal vs Effective Interest Rate in Excel (2 Practical Examples)
Conclusion
This article explained in detail how to calculate Future Investment Value with Initial Investment and without Initial Investment along with the Inflation, Tax and Interest Rates in Excel. I hope this article has been very beneficial to you. Feel free to ask if you have any questions regarding the topic.
Dear Sanjida, I would like to express my sincere thanks to you, for providing an updated solution to the abovementioned challenge, which now takes one’s tax rate into consideration as well. Job very well done. I can now obtain the “full picture” regarding possible investment scenarios.
Kind regards
Maurice
Hello Maurice,
Really glad to help you out. Don’t hesitate to ask if you need any further assistance. Thanks.