Member Avatar for VeroSFO

As will become evident, I am new at this. But I will try to be brief and to the point. In short: we have designed a life insurance policy whose premiums are paid by loans. The accrued annual loans + the accrued interest is paid ONLY at the death of the borrower. I am [obviously] not an Excel knowledable person. Some key points are... and then my question...

1) I have a Master (unprotected) Excel workbook (i.e., xls) by one of our employees who is no longer with us. It shows a payment ("PMT") that must be paid every year (or for just 5, 10 years);

2) the PMT is made by taking out a loan at whatever interest rate I wish to use (interest accrues & is pegged annually, and may stay the same until the borrower's age 100. I could also change every 5 years, etc., BUT...for this question assume the int. rate will be "x" from age 60 to age 100 and not change;

3) the loan + accrued interest is to be paid ONLY at the death of the borrower.

I want to change the current interest rate shown to another number. It is currently 6%. How can I do that AND have it change every other page that uses that calculation?

I understand I can give my personal e-mail address and will so advise it is: . I am also happy to call someone that could help me, but I guess that is frowned on...

Thank you to whomever answers... Regards, VeroSFO

  • 2 Contributors
  • 1 Reply
  • 120 Views
  • 1 Week Discussion Span
  • Latest Post Latest Post by MartyMcFly

Dani AI

Generated

Good start from — centralize the rate so one change updates the whole model. A more robust, low-effort way is to put the rate on a single "Inputs" (or "Parameters") sheet and give that cell a name (for example InterestRate) so every formula can refer to the name instead of a literal. Enter the value as a true percent (6% or 0.06) and format the cell as Percentage to avoid accidental 600% entries.

Practical steps to convert the existing workbook:

  • Make an Inputs sheet and put the labelled rate cell there. Name the cell with the Name box or Define Name.
  • Use Excel Find (Ctrl+F) set to Within: Workbook and Look in: Formulas to locate any hard-coded "6%" or "0.06". The Find All list shows sheet and cell addresses so you can jump straight to each formula and replace the literal with the named range (or an absolute reference).
  • Use Formulas -> Trace Dependents on your rate cell to see which cells reference it on the same sheet; cross-sheet references show dotted arrows so you know where to look next.
  • If the file contains macros, open the VBA editor (Alt+F11) and search for numeric literals like 0.06 or "6%" — VBA can contain hard-coded rates independent of worksheet cells.

Quick tips and cautions:

  • Back the workbook up before bulk edits. Find/Replace cannot safely convert numeric literals inside formulas to a named reference automatically in every case — manual checks are required.
  • If the interest should vary by age or period later, build a small rates table and pick the proper rate with INDEX/MATCH or VLOOKUP rather than hard-coding. Test a few scenarios after you change the one input to confirm all sheets update as expected.
Member Avatar for MartyMcFly

Unfortunately you need to change a lot, but only this first time.

Insert a cell that has interest rate, say E6. Every time you refer to it in a calculation put E6 rather than the Interest Rate. You can do this in two ways as follows;

1. Put E6, for example =C2*E6/100
2. Select the cell ie. =C2*(then left click on the cell E6)/100 - (useful if using seperate sheets).

Every time you need to change interest rates, change the value in E6

Hope this was the level of detail you were after, and what you wanted to achieve.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.