Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Increasing the number of payments in an anortization table

0

I have a simple amortization table that was setup last year for a loan to my son.   It was originally set up to run for three years with a payment being made every month, however, as he has been unable to pay for a while I need to extend the length of the loan.  

If I simply replicate the bottom row of the table it the formulae do not work and return '#VALUE' instead of the relevant dollar values.     I believe it has to do with the added payment number being greater than'@Pay_Num<>"" but there-after I am stumped.

Addendum:   Okay, I have gotten rid of the '#VALUE' errors by redefining the cell ranges (by increasing the row values by 12) in the affected cells by using the Name Manger, however, whilst this does allow me to replicate the formulae in the additional 12 rows it still does not populate cells with any data.   I have attached the revised sheet which is named  '**Ed 3 - Test Sheet'.

Answer
Discuss

Answers

0
Selected Answer

Hi geodav52,

I gave your file a look but could not find any obvious reason for this condition. So I did a Google search to see if I could find the cause. Of all the "fixes" posted, none of them were able to resolve your issue. Do other Excel files have this isuue? If so it is probably an Excel issue. If not, this file may be corrupted in some way. Did you install an Excel add-in before this issue arose? If so, try un-installing to see if that fixes it.
Then I noticed that in Payment #38 row (60) the ending balance was $0.00. So in the Extra Payment column I entered -250.00 to create a positive balance and PRESTO - all Payment #39 details showed up. Based on this, it must/might have to do with your " IF " formulas where the "if false" result is "  ""  ". I don't have the time to inspect the formulas but that might be a place to check.

You use  O365 and I have 2016 so this is about all the assistance I can offer.

As a side note, you can make putting a check mark in column K (Confirmed) easier than using an image. Format the cells K26:K69 as "Wingdings2" and size 16. The type an upper case " P " and a checkmark will appear. Type an upper case " O " to get an X.

Update (20:30)

Had a few minutes to investigate further. For all rows below and all columns to the right of the table the issue does not exist - only within the bounds of the table. Also, rows 1 thru 22 do not experience this issue. It is only the range A23:J69 that has the issue.

Also, one at a time, I changed the " "" " at the end of the " IF " formulas to "No" but nothing changed. I then deleted all formulas in rows 60, 61, & 62 but the issue did not go away. So I am now leaning towards it has something to do with the way the table ($A$1:$J$69) is formatted.

Update (21:30)

I think I have found it. I decided to check what Conditional Formatting there might be in play. The first rule in the list has the font colour formatted as WHITE. I changed this to automatic and VOILA - everything appeared. This in conjuction with the $0.00 balance in Col " I " is what is causing the issue. So now you have to decide if you can live with this or want to change the CF rule. Also, you can make your CF rules shorter and easier to understand. Example: change your formula rule: "=IF(ROW(A23)>Last_Row,TRUE, FALSE)" to be just: "=ROW(A23)>Last_Row" (and the same for the other 2 rules). With this shorter version, if it returns a "True" result the formatting will be applied, if not it won't be.

If this solves things for you please mark my answer as selected. If not, maybe John will come up with a better solution.

Update 11/2/24

Even though I don't have any current need for a loan repayment table, I decided to have a go at creating one better than the one you have. The one I made is simpler and the only CF (yellow fill) is for the cells that need data input. Enjoy. Feel free to adapt/modify as you see fit to suit your needs.

Cheers   :-)

Discuss

Discussion

Thanks, WillieD24, your assistance has been spot-on and appreciated.   This table was one that I downloaded from the web thus I did not even think of checking out the conditional formatting. I should have created one myself instead of being lazy as at least then I would know how it was put together,.

Again, thanks for your help.
geodav52 (rep: 4) Oct 28, '24 at 12:39 am
geodav52,

Glad I was able to figure it out for you. It's been my expeience that if you ask enough "What if?" questions you will arrive at the answer. Thanks for selecting my answer.

Interestingly, at $700 monthly with 7 missed payments the loan only takes 37 months to reach a zero balance - not 4 years. While at $600 monthly and 7 missed $600 payments takes 43 months to pay off.

Cheers   :-)
WillieD24 (rep: 657) Oct 28, '24 at 9:36 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login