Excel Formula obtain outstanding balance from one sheet and subtract PMT and get new balance for following Month.

GSILVA66

New Member
Joined
Mar 16, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
  2. Web
I have a Workbook with 13 Sheets named TUITION, JANUARY, FEBRUARY, MARCH and so on till December. Trying to enter a Formula to get ending balance from January, subtract February Payment and get the new outstanding balance for February and repeat every Month, until hole tuition is paid off. . All values are fixed amounts, no interest. PMT arrangement vary, some are Weekly, Bi-Weekly or Monthly.

sory fi I could not be more clear, I am new to MrExcel and I am a beginner in Excel.

what I am trying to do is create a formula to get January ending balance and enter it i a cell in February to get the principal amount and subtract the amount paid in February to find the Outstanding balance and repeat Month after Month until reach Zero balance. This is the formula I am trying to work with, = IFERROR(IF(($D$4-E8)=0, 0, ($D$4-E8)),JAN!E8-F8)

NEW FEE TUITION COLLECTION VANGUARD.xlsx
ABCDEFGH
3STUDENT TUITION COLLECTION SYSTEM
4TUITION$4,575.00A.Y.2021
5
6WEEK 1 PMT AMTWEEK -2 PMT AMTWEEK-3 PMT AMTWEEK-4 PMT AMTTOTAL PAIDBALANCE DUE
7MONDAY/WEDNESDAY
8ANKIE B 4575PAID IN FULL
9FABIAN O$50.00$50.004575
10JUDE A$300.00$300.004575
11PANKY J 4575
12LOUDIA T$1,000.00$1,000.004575
13MAGALY P  4575
14MARTHA O$1,000.00$1,000.00$2,000.003575
15PATRICIA J 4575
16ISLANDES C 4575
17ROSELINE M 4575
18TUESDAY 4575
19ROMMEL Y 4575
20SCHIFANE P 
21ANA C 
22ANDREA M 
23FANIE S 
24IGOR B 
25FRIDAY 
26PAULA H 
27GERARD D 
28GUSTAVE L 
29ALGERTE E 
FEB
Cell Formulas
RangeFormula
G8:G19G8= IFERROR(IF(($D$4-E8)=0, 0, ($D$4-E8)),JAN!E8-F8)
F8:F29F8=IF(SUM(B8:E8)=0, "", SUM(B8:E8))
 
Last edited by a moderator:

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Watch MrExcel Video

Forum statistics

Threads
1,130,210
Messages
5,640,869
Members
417,174
Latest member
diegomuser

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top