I'm trying to calculate how many quarterly payments will fall in a defined fiscal year but can't figure out how to do it.
If I enter into a lease that starts on 1/1/07 I will have four payments in 2007. My payments are set up as quarterly in arrears so the first one will be due 4/1/07 the second will be due 7/1/07, the third 10/1/07 and the last on 1/1/08. Simple. Problem is, if I enter into a lease in some month other then January then I will end up not making 4 payments this year. I'd make at most 3 payments.
For cash flow purposes, I need to project into the future how many payments I will make on each lease each fiscal year. It will be four payments for each year other then the first and last year the lease is still active. To make matters worse, my fiscal year is 9/1 through 8/31 and leases are signed each month and generally with terms of 5 to 7 years.
The spreadsheet I use to track them includes a column for the first payment date, the last payment date (the last payment at the end of the lease term), the number of payments over the term, say 28 for a 7 year lease, and the payment amount per quarter. Then off to the right I simply fill in columns with a formula multipling the payment amount by the number of payments that will occure that fiscal year. One column per year 8 or so years into the future. Each column heading included the fiscal year i.e. "FY 2008".
I'm hoping someone has developed a formula that can determine how many payments will occure in each of the next 8 or so fiscal years based on the information I manually plug in now.
If I enter into a lease that starts on 1/1/07 I will have four payments in 2007. My payments are set up as quarterly in arrears so the first one will be due 4/1/07 the second will be due 7/1/07, the third 10/1/07 and the last on 1/1/08. Simple. Problem is, if I enter into a lease in some month other then January then I will end up not making 4 payments this year. I'd make at most 3 payments.
For cash flow purposes, I need to project into the future how many payments I will make on each lease each fiscal year. It will be four payments for each year other then the first and last year the lease is still active. To make matters worse, my fiscal year is 9/1 through 8/31 and leases are signed each month and generally with terms of 5 to 7 years.
The spreadsheet I use to track them includes a column for the first payment date, the last payment date (the last payment at the end of the lease term), the number of payments over the term, say 28 for a 7 year lease, and the payment amount per quarter. Then off to the right I simply fill in columns with a formula multipling the payment amount by the number of payments that will occure that fiscal year. One column per year 8 or so years into the future. Each column heading included the fiscal year i.e. "FY 2008".
I'm hoping someone has developed a formula that can determine how many payments will occure in each of the next 8 or so fiscal years based on the information I manually plug in now.
