How to pull data based on today's date from a monthly payment schedule in excel?

[ajg12]

I am new to this forum so hopefully I am going about this the right way.

In excel I have a payment schedule for a loan with several vertical columns. The far left column is a list of payment dates that are arranged one month apart, ex: 03/01/2016, 04/01/2016, 05/01/2016, and so on down the column. In the far right column I have the remaining balance of the loan after each months payment, ex: $72,500, $72,000, $71,500 ect...

What I am trying to do is create a formula that pulls the remaining loan balance (far right column) based on what "todays" date is. In other words I want to be able to open up the model on any given day and know how much my balance is on that particular day. But I don't know how to correlate "todays" balance based on a monthly payment schedule that only shows the first day of each month.

I can't seem to figure out how to do it, I m thinking it needs to be an "if then" function with a vlook up. Any help is greatly appreciated.


Thanks

 

[iconiuss]

A VLOOKUP is what your after. To get the 1st of the current month use DATE

=DATE(YEAR(TODAY()),MONTH(TODAY()),1)
So it uses the current year, and current month, but the 1st day.

As I don't know how many columns you have, I've written this for 2 columns.

=VLOOKUP(DATE(YEAR(TODAY()),MONTH(TODAY()),1),A1:B12,2)

Hope this helps.