SOLVED

Adding and stopping payments. How to make a reference cell the number of year a payment is paid

%3CLINGO-SUB%20id%3D%22lingo-sub-3196400%22%20slang%3D%22en-US%22%3EAdding%20and%20stopping%20payments.%20How%20to%20make%20a%20reference%20cell%20the%20number%20of%20year%20a%20payment%20is%20paid%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196400%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20find%20a%20way%20to%20add%20up%20different%20financing%20and%20paybacks.%20The%20%223%22%20in%20the%20input%20cell%20is%20the%20number%20of%20years%20each%20payment%20will%20be%20paid%20back.%20The%20different%20payments%20are%20below%20the%20%223%22.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20the%20bank%20payback%20cells%20to%20be%20able%20to%20add%20them%20and%20then%20stop%20once%20they've%20reached%20the%20allotted%20number%20of%20payments.%3C%2FP%3E%3CP%3ESo%20if%20it%20was%203%20years%20per%20payment%20The%20Bank%20Payback%20cell%20would%20look%20like%3A%3C%2FP%3E%3CP%3EYear%201%3A%20Payment%201%3C%2FP%3E%3CP%3EYear%202%3A%20Payment%201%2B%202%3C%2FP%3E%3CP%3EYear%203%3A%20Payment%201%2B%202%20%2B%203%3C%2FP%3E%3CP%3EYear%204%3A%20Payment%202%2B%203%20%2B4%20(The%20first%20payment%20has%20been%20paid%20off)%26nbsp%3B%3C%2FP%3E%3CP%3EYear%205%3A%20Payment%203%2B%204%20(The%20second%20payment%20has%20been%20paid%20off)%3C%2FP%3E%3CP%3EAnd%20so%20on.%20However%2C%20they%20need%20to%20be%20able%20to%20adapt%20to%20if%20the%20number%20of%20years%20per%20payment%20is%20changed%20and%20if%20new%20payments%20are%20added%20as%20well.%3C%2FP%3E%3CP%3EThis%20can%20be%20done%20via%20functions%20or%20VBA.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22crimsonb_0-1645499382661.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F350160iBF972723D1FEC5EF%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22crimsonb_0-1645499382661.png%22%20alt%3D%22crimsonb_0-1645499382661.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3196400%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3196832%22%20slang%3D%22en-US%22%3ERe%3A%20Adding%20and%20stopping%20payments.%20How%20to%20make%20a%20reference%20cell%20the%20number%20of%20year%20a%20payment%20is%20paid%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3196832%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1314109%22%20target%3D%22_blank%22%3E%40crimsonb%3C%2FA%3E%26nbsp%3BNot%20the%20most%20elegant%20and%20dynamic%20solution%20but%20with%20some%20(hidden)%20helper%20columns%20I%20could%20achieve%20what%20you%20asked%20for%2C%20I%20think.%20But%20it%20requires%20some%20manual%20adjustments%20if%20you%20want%20to%20expand%20the%20schedule.%20See%20if%20the%20attached%20example%20is%20something%20that%20could%20work%20for%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to find a way to add up different financing and paybacks. The "3" in the input cell is the number of years each payment will be paid back. The different payments are below the "3". 

I need to the bank payback cells to be able to add them and then stop once they've reached the allotted number of payments.

So if it was 3 years per payment The Bank Payback cell would look like:

Year 1: Payment 1

Year 2: Payment 1+ 2

Year 3: Payment 1+ 2 + 3

Year 4: Payment 2+ 3 +4 (The first payment has been paid off) 

Year 5: Payment 3+ 4 (The second payment has been paid off)

And so on. However, they need to be able to adapt to if the number of years per payment is changed and if new payments are added as well.

This can be done via functions or VBA.

crimsonb_0-1645499382661.png

 

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@crimsonb Not the most elegant and dynamic solution but with some (hidden) helper columns I could achieve what you asked for, I think. But it requires some manual adjustments if you want to expand the schedule. See if the attached example is something that could work for you.

@Riny_van_Eekelen This works! Thank you so much! I really appreciate it.