|
Calculate the Future Value (FV) of Compound Interest in Excel - UDF Macro
This UDF (user defined function) calculates the Future Value of Compound Interest in Excel. The mathematical formula that is behind this function is PV * (1 + r) ^ N. This is a great function to include in your workbook if you often work with financial calculations such as the future value of invested sums of money or other assets. This adds functionality to Microsoft Excel where it currently does not exist. This is a great financial UDF for Excel.
Where to install the macro: Module
UDF to Calculate the Future Value (FV) of Compound Interest in Excel
Function FVCOMPOUNDINTEREST(PV As Double, r As Double, N As Double) As Double
FVCOMPOUNDINTEREST = PV * (1 + r) ^ N
End Function
How to Install the Macro
- Select and copy the text from within the grey box above.
- Open the Microsoft Excel file in which you would like the Macro to function.
- Press "Alt + F11" - This will open the Visual Basic Editor - Works for all Excel Versions.
Or For other ways to get there, Click Here.
For Excel Versions Prior to Excel 2007
Go to Tools > Macros > Visual Basic Editor
For Excel 2007
Go to Office Button > Excel Options > Popular > Click Show Developer tab in the Ribbon. Then go to the Developer tab on the ribbon menu and on the far left Click Visual Basic
- On the new window that opens up, go to the left side where the vertical pane is located. Locate your Excel file; it will be called VBAProject (YOUR FILE'S NAME HERE) and click this.
- If the Macro goes in a Module, Click Here, otherwise continue to Step 8.
- Go to the menu at the top of the window and click Insert > Module
- Another window should have opened within the Visual Basic Editor's window. Within this new window, paste the macro code. Make sure to paste the code underneath the last line of anything else that is in the window.
- Go to Step 8.
- If the Macro goes in the Workbook or ThisWorkbook, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Then, at the bottom of the list that appears, double-click the ThisWorkbook text.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Go to Step 8.
- If the Macro goes in the Worksheet Code, Click Here, otherwise continue to Step 8.
- Directly underneath your excel file called VBAProject(your file's name here), click the Microsoft Excel Objects folder icon to open that drop-down list.
- Within the list that appears you will see every worksheet that is in that excel file. They will be listed as such: Sheet1(NAME OF SHEET HERE) and under that will be Sheet2(NAME OF SHEET HERE). Select the sheet in which you want the macro to run and double-click that sheet.
- A new window inside the Visual Basic Editor's window will appear. In this new window, paste the code for the macro. Make sure to paste this code underneath the last line of any other code which is already in the window.
- Repeat steps b and c for every sheet you want the macro to work in. Putting the macro in one sheet will not enable it for any other sheets in the workbook.
- Go to Step 8.
- Close the Microsoft Visual Basic Editor window and save the Excel file. When you close the Visual Basic Editor window, the regular Excel window will not close.
- You are now ready to run the macro.
Similar Helpful Excel Resources
Hi
I want to know the function to calculate the SI and the CI in Excel. I know manually we calculate the Simple Interest = P*R*T and compound Interest = P(1+i)^n . When I calculate the SI using inbuilt function of Excel i.e. PMT as shown in the following link
http://www.ehow.com/how_4813646_calc...est-excel.html
It given right answer. But if on same data i apply P*R*T it gives something different. Also I saw following site
http://www.busysoftorder.com/npv/1.htm
it uses FV function. Kindly advice how to calculate the Si and CI using inbuilt functions. I just want to calculate it when there is no change in the rate of interest in the subsequent years and it will remain same.
Regards
Harbinder Singh
I am trying to set up my excel to calculate daily compound interest.
The amount is 10,000 at 0.75% per day for 6 months.
I have tried several different things with no success - help please
Example, If I bought a property 5 years ago for $134,000 and it's now worth $210,000. What formula will calculate the compound growth rate for the past 5 years?
I can do it on a scientific calculator but can't seem to get excel to do cube roots, 4th roots etc
Thanks for any help provided.
Jeff
Hi there. I'm trying to come up with a way of calculating Money earned over time by compounding interest, but with a twist. After reaching a set amount of money, all money above and beyond does not gain interest. example:
Principal: $120 (user input value)
Duration: 9 (user input value in days, compounding daily)
%Intertest: 4% (user selected value, either 2% or 4%)
Max interest you can earn: $6 (fixed)
Max interest generating money: $150 (variable dependant on %interest, = $150 or $300)
Response/Answer is final value. I don't need the daily results like the example.
Result would be: $170.84
$124.80 (4.80 interest)
$129.79 (4.99)
$134.98 (5.19)
$140.38 (5.40)
$146.00 (5.62)
$151.84 (5.84)
$157.84 (6.00 reached the max interest level)
$163.84 (6.00)
$170.84 (6.00)
my equations I have so far only do one (below 150 total) or the other (above) but not both.
and its just a regular formula: = IF (P<M, IF (P*I^D<M+1,P*I^D,"over limit"),P+6*D)
P=Principal
D=Duration
I=Interest+1 (so 4% becomes 1.04)
M=Max interest making money
Please go easy on me... its been 10 years since I really got into difficult/challenging formulas in excel and vba... I'm pretty rusty, but pick up quick.
I'm thinking this is going to be a custom function, or maybe a module, but not sure.
Thanks!
Hi, I have one large file and I need to compute the final value of investing a particular amount based on the returns given by the stock market. I'm trying to figure out a way that I can compute the answer without going into cell after cell with the same kind of formula. The formula that I'm looking for is like the formula for a compound interest. I.e. Final value = P(1+r)^n. However, in my case, r is always changing and hence P is also changing as well. What I'm currently doing is typing the same kind of formula into each cell. For example to find the year-end value I input: 1000(1.05) then the next year, the year-end value is this: (1000)(1.05)(1.10), and the next may be (1000)(1.05)(0.96)...and so on....does anyone know of a simpler way to do this?
How to calculate past due fees on balances over 30 days past due?
The balances are past due for 1 to 40 or more months. The monthly payment is 1500.00 and a 10% past due fee is to be added to the balance each month plus the previous months interest. Is there a formula to show interest each month and the new balance by month?
How can I calculate the amount I would receive in compound interest on a
fixed amount at a fixed interest rate for a fixed peirod. Should be easy !
I'm trying to find an elegant way of calculating compound interest on outstanding debts; one big problem is that clients occasionally make ad-hoc payments so I need to maintain a running total, using a changing bank base lending rate. I have taken quite a long look round and it seems this question has not come up before, I hope!
|
|
