I need to calculate short term loan payment balances (12 months). I have included my worksheet and the tables associated with it along with an explanation. I need the formula to calculate the principal payments from each table for the next 12 months after the date in B1 (or the current ending balance if there are less than 12 payments due).
For example, based on the attached, #76 should have short term of $10,582.62 (Feb 23 - Jan 24 principal payments).
Thanks in advance!
Last edited by kafarrell; 01-19-2023 at 11:50 AM. Reason: To add clarification
Please update your profile (on User CP at the top of the screen) to show the version of Excel that you are currently using. The formulae in column G of your sample file are using XLOOKUP, which was not available in XL2010.
Pete
Changed... Office 365.
Can you help me now?
When I changed the date to 2/28/23 in Cell B1 in order to calculate short term, it did not change the amounts in Column I.
Your data tables are nicely organised... Table 76 being the table name for Number 76. So... Try:
=SUMIFS(INDIRECT("Table"&A3&"[Principal]"),INDIRECT("Table"&A3&"[Date]"),">="&TODAY(),INDIRECT("Table"&A3&"[Date]"),"<"&EDATE(TODAY(),12))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
I figured it out! Changed Today in the formula to $B$1. Thank you!!!
Last edited by kafarrell; 01-31-2023 at 10:41 AM.
Woo Hoo! Thanks for the feedback!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks