Loan Payment Schedule With Variable Start Date

[thisismyname2]

Hi guys, got close to solving this on my own but I think I need one last piece of advice. Created a loan payment schedule that displays the payments made during the year. The yellow highlighted cell adjusts when the loan payment starts. So the value '2' in cell E2 means the loan takes place in the year 2003.

Looks like I need a formula that returns the first non-zero cell address in column B, 'Beginning Principal' and make it an absolute cell reference. For example, the PV in the CUMIPMT formula is currently static and needs to be dynamic:

=-CUMIPMT($B$2/12,$B$3*12,$B$11,((A11+1)-($E$1+$E$2))*12-11,((A11+1)-($E$1+$E$2))*12,0)

I thought that returning the largest number in B9:B38 would work using the MAX function, but it returns a circular reference error. If there is an easier way to make this work, please let me know... Also, putting in the value '1' in cell E2 gives a circular reference error too that I can't fix. Thanks in advance for your help!

Variable Loan Payment Start Date.xlsx

[Md Aejaz]

HI,

Use the PMT function
=PMT(6/12,20*12,5000000) this will give the EMI, deduct the Principal (EMI- Interest) from over all principal to get the remaining balance of all the periods. Remember the interest will be charged only to the remaining outstanding principal amount.

[thisismyname2]

Hi thanks for your reply but I think the point is being missed. Just need a formula that returns the 5,000,000 in column B, which will be variable based on the start date. Right now the PV in the CUMIPMT function is being pointed in the correct cell ($B$11), but that needs to change based on what is being entered in cell E2. Thanks!

[thisismyname2]

Found the solution, ended up using the OFFSET function to grab the appropriate cell #.