Can a cell reference it’s own column number?

Solved3.57K viewsTips and Techniques

I’m working on a financial model, where a loan payment is calculated for a term. However, no payment is due for the first X months (e.g. 24 months).

This type of loan has a 6 year term, but since no payment is due for the first 2 years, I use a loan amort period of 4 years.

I’ve got a matrix with columns for each month (month 1, month 2, etc), and I want to fill in the loan payment amount. However, I want to the function to only fill in a payment value if the column it is in is more than the first N months (e.g. 24 months).

Can a cell figure out it’s own column number somehow, so I can use an IF function to either return zero (you’re in the first 24 columns), or a payment amount (column >= 25]

For instance:

If(month[>=no_payments_period], pmt_function, 0)

Thanks for any tips!


Found it! Just in case this is useful to others, you solve it using the # function in the formula. Here’s what worked for me:

Owner Holdback = if((#Month > Purchase Breakdown::Delay before payments:Owner Holdback), Purchase Breakdown::Payment:Owner Holdback, 0)