Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 07-26-2020, 02:42 PM
revans611 revans611 is offline How do cell events in the "Loan Amortization Schedule" template work? Windows Vista How do cell events in the "Loan Amortization Schedule" template work? Office 2007
Novice
How do cell events in the "Loan Amortization Schedule" template work?
 
Join Date: May 2009
Location: Houston, TX
Posts: 9
revans611 is on a distinguished road
Question How do cell events in the "Loan Amortization Schedule" template work?

I am trying to learn how Microsoft's "Loan Amortization Template" works in my Office 365 version of Excel.





If the user enters a number in the cell named LoanPeriod, or in the cell named PaymentsPerYear, the spreadsheet automatically recalculates the number of payment lines to display. How does that happen? I can find no VBA behind code for the spreadsheet.
Reply With Quote
  #2  
Old 07-26-2020, 06:02 PM
trevorc trevorc is offline How do cell events in the "Loan Amortization Schedule" template work? Windows 7 32bit How do cell events in the "Loan Amortization Schedule" template work? Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Its all done with mirrors . no it's done with Excel formula's in each cell and this gives you the result.
Reply With Quote
  #3  
Old 07-26-2020, 08:08 PM
revans611 revans611 is offline How do cell events in the "Loan Amortization Schedule" template work? Windows 10 How do cell events in the "Loan Amortization Schedule" template work? Office 2019
Novice
How do cell events in the "Loan Amortization Schedule" template work?
 
Join Date: May 2009
Location: Houston, TX
Posts: 9
revans611 is on a distinguished road
Default

Er, I was looking for a little more detail. For example, if the user changes the Loan Period In Years from 1 to 12, with the payments per year already set to 12, then 28 lines for payments are added to the spreadsheet. Where's the formula for that? It's not in the cell for the Loan Period In Years.
Reply With Quote
  #4  
Old 07-26-2020, 09:16 PM
trevorc trevorc is offline How do cell events in the "Loan Amortization Schedule" template work? Windows 7 32bit How do cell events in the "Loan Amortization Schedule" template work? Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

all the cells are prefilled, click on an empty one and you will see the formula in it. i didn't check to see how many lines are prefilled but expect it will be a lot.
Reply With Quote
  #5  
Old 07-27-2020, 12:33 PM
revans611 revans611 is offline How do cell events in the "Loan Amortization Schedule" template work? Windows 10 How do cell events in the "Loan Amortization Schedule" template work? Office 2019
Novice
How do cell events in the "Loan Amortization Schedule" template work?
 
Join Date: May 2009
Location: Houston, TX
Posts: 9
revans611 is on a distinguished road
Default

Quote:
Originally Posted by trevorc View Post
all the cells are prefilled, click on an empty one and you will see the formula in it. i didn't check to see how many lines are prefilled but expect it will be a lot.

Again, if I click in the cell for loan period in years, I see a number, not a formula. Also, it appears that no lines are prefilled. The number of prefilled lines is controlled by the loan period and payments per year.



I have attempted to upload the workbook. I hope it made it.
Attached Files
File Type: xlsx Loan amortization schedule1.xlsx (54.1 KB, 9 views)
Reply With Quote
  #6  
Old 07-27-2020, 12:37 PM
trevorc trevorc is offline How do cell events in the "Loan Amortization Schedule" template work? Windows 7 32bit How do cell events in the "Loan Amortization Schedule" template work? Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Click on row 50, you WILL see the cell formula
Reply With Quote
  #7  
Old 07-28-2020, 12:38 PM
Purfleet Purfleet is offline How do cell events in the "Loan Amortization Schedule" template work? Windows 10 How do cell events in the "Loan Amortization Schedule" template work? Office 2019
Expert
 
Join Date: Jun 2020
Location: Essex
Posts: 345
Purfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to beholdPurfleet is a splendid one to behold
Default

As TrevorC has said all formulas are there but with an IF that 'turns off' the formula if the payment number is exceeded.


the formulas run down to row 371
Attached Images
File Type: png 371.png (23.2 KB, 7 views)
Reply With Quote
  #8  
Old 07-28-2020, 12:45 PM
trevorc trevorc is offline How do cell events in the "Loan Amortization Schedule" template work? Windows 7 32bit How do cell events in the "Loan Amortization Schedule" template work? Office 2013
Competent Performer
 
Join Date: Jan 2017
Posts: 174
trevorc will become famous soon enoughtrevorc will become famous soon enough
Default

Thanks for the backup, some ppl are hard convince
Reply With Quote
Reply

Tags
events

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Daily Loan Tracking schedule pranjal Excel 1 12-04-2014 11:57 AM
How do cell events in the "Loan Amortization Schedule" template work? Specifying "time of day" events DavidHarris Project 3 05-26-2014 07:21 AM
'Linking' entered information to other "cells" from an original "cell" in MS Word Wade Word 6 09-03-2012 05:22 PM
How do cell events in the "Loan Amortization Schedule" template work? How can I paste cell "A1" contents to cell "B1" if cell "A1" is not blank? Learner7 Excel 1 04-25-2011 04:39 AM
Template "File In Use" when opening 2 documents based on the same template wendt Word 5 12-15-2009 12:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:50 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft