Create a variable that stores value in excel

0
votes

Can I create a variable in excel maybe by using macro or anything more simpler, which stores my value and then uses that going ahead in formula,

My main problem is, I am trying to make a formula for calculating Compound Interest monthly, so for Example: I invest Rs.2,00,000 @ 10% p.a interest rate. In normal method I have to do:- 200000*(10%/12) which will give 1666.67, Now i will add (200000+1666.67)*(10%/12) which will give one value and so on. So i want to create something that can store the Principle amount and Interest each months.

Also i cannot use compounding formula since, lets say in first month i invested only for last 5 days of the month, then interest for first month will be calculated only for 5 months, and then computation of balance month will proceed. Thanks in Advance. Do let me know if you want any more clarity.

2
excelexcel-formulavba
you can store it as a name for example (from Excel 2010 (i think) it's Formulas tab, then Name Manager), no macros/vba needed – tsdn
I can store a formula in define name, but then how can i use its changing values in my formula? Can you give me an example of what kind of formula you are suggesting me to use.? – Palash Gangal
I didn't suggesting storing formula in a defined name, because, as far as i understood your question, it is not what you asked for – tsdn
Yes you got it right, its not storing formula what i asked for. So how are you suggesting i should go ahead with it? What i should store in Name manager? – Palash Gangal
Sorry if I misunderstood the ask. Check out Chris' answer below. You can reference a cell (address) in a formula, i.e. If A1 has a value of 201666.67, you can write your "=201666.67*(10%/12)" formula as "=A1*(10%/12)" with the same result. Is this what you need? – tsdn

2 Answers

2
votes

By far the simplest way of storing a static value in Excel, without using any VBA at all, is just to store that value in a single cell. That cell may then be referenced in any formula.

For example if you stored a static value in cell A1, you would reference that value in formulæ as $A$1.

If you don't want the static value on the same spreadsheet on which you're working, then you can create a new sheet named "Static" (or whatever you like) - then reference the cell as Static!$A$1. The sheet may then be hidden if you like.

You speak about adding principal amounts and compound interest. The easiest way to do this would be in a simple table, where the first row contains the initial calculation (including a reference to the static value as above); then subsequent rows reference the row above by means of formulæ. Therefore values would be added and multiplied according to a running total from the row above.

From your question, the specifics are not clear - so if you need more help than this, you'll have to give an example of what actual output you want - including several example rows.

1
votes

You can also create/use a Named Range in your formula. To create a Named Range:

Formulas > Name Manager > New (Or type name in Name Box as seen in below photo)

enter image description here

This will create a locked reference to a cell (or block of cells for other scenarios) that can be referenced in a formula by name. Make sure this cell has the value you want to reference of course.

For instance, if you name your range Principal, you can refer to the range (value) in a formula as such:

= 2,000 * Principal

This is essentially the same solution as mentioned above, but it may be more intuitive to reference a named range (Principal) rather a locked cell such as ($A$1).