How to Display Current Balance & Remaining Payments at Top of Amortization Schedule?

Hi all,

I've built an amortization table and would like for it to tell me what the current balance of the loan is by filling in a cell at the top of the sheet based on today. I would also like it to tell me how many payments are remaining based on today as well. I have attached my workbook to this post. You'll notice where the two empty cells are at the top of the sheet that I would like to fill in (text highlighted in red). I tried using an IF statement to come up with the current balance, but couldn't figure out how to make it skip to the next row if the first row was false. Any help would be greatly appreciated!

Thank you.

Loan Amortization Schedules.xlsx

Marcol, yes, this is exactly what I'm looking for! Thank you! However, there are a couple of issues with it.

1) If you remove the extra payments in the "Extra Payment" column, the "Remaining Payments" cell shows as #N/A.

2) If the first payment hasn't occurred yet, the "Remaining Payments" and "Current Balance" show as #N/A. You'll see this occur if the "Loan Commencement Date" is set to 5/25/13 because the loan start date would then be 6/25/13 (one month later) which hasn't occurred yet. If this were the case, the "Remaining Payments" should read "360" and the "Current Balance" should read "$10,000." However, now that I'm typing this, I realize that it wouldn't be able to reference the amortization schedule until after the first payment has been made since the ending balance in payment one is $9,990.04, not $10,000. Is there a way to say something like, "If the first payment hasn't occured, show loan value and number of payments"? I hope that made sense.

Marcol, this works perfectly! Thanks for your help! Final question, if I have multiple sheets in my workbook that each have a different loan, how can I make it so that there is only one "Name" in the "Name Manager" so I don't have to create "Amort_Table1" "Amort_Table2" "Amort_Table3" etc.? In summary, how can I make the Name Manager reference the current sheet instead of having to specify a specific sheet?

How about if you pay on it twice a month (bi weekly)?