CODEWITHSUNDEEP
excelexcel-formulavba
Palash Gangal
Palash Gangal

Reputation: 11

Create a variable that stores value in excel

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.

Upvotes: 0

Views: 2316

Answers (2)

urdearboy
urdearboy

Reputation: 14580

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).

Upvotes: 1

Chris Melville
Chris Melville

Reputation: 1518

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.

Upvotes: 2

Related Questions