finding and reporting in column A, where a series of column reaches zero

[Gary Tamblyn]

Here's the Scenareo,

I've produced a chart that calculates when a loan would
get paid off.

The First column holds all the values needed in the chart
(interest rate, monthly payments etc...)

These values then get copied across columns that are dated
monthly at the top of each column. (approximately 60
columns, representing 5 years)

obviously each column would calculate the appropriate
interest values, and automatically deduct any payments
made that month.

Somewhere along the columns (hopefully) the "amount to
pay" value would reach zero, or a negative number. This
would indicate the loan has been paid off. However, and
this is the problem:

I don't want to be scrolling back and forth looking for a
column that has reached zero, I would like to report the
date, that is at the top of this specific 'loan paid off'
column, In column 'A'

That is to say, I could adjust the series of values in
Column A, and see how this would affect the finish date of
the loan, so if the 'amount to pay' row reached zero at
month 24 (Column 'O' with month 1 starting at 'B'), The
date at the top of column 24 would appear somewhere in
column 'A'.

I guess I would need to use a macro, but is there a clever
way of doing it with formulas ?

Thank you in advance
Gary Tamblyn.

 

[Steve Smallman]

Gary,
the functionality you are looking for is Net Present Value of an Annuity,
but in excel there is a function called NPER
lets assume that you have the following data

A B C
1 Interest Rate 12% Per annum
2 Repayment Schedule Monthly
3 Loan Amount 30,000
4 Repayment 1000 per month
5 first repayment pick a date
6 last repayment

in cell B6 enter the formula "=NPER(B1/12,-1*B3,B4)*30.4+B5" and format B6
as mmm yy

NPER will return the number of periods (in this case 35.85)
*30.4 converts months to days (roughly)
+B5 adds the start date
and this should be pretty close. You can then check amortisation schedule to
make sure

Steve

 

[Gary Tamblyn]

Thanks Steve,
The Function worked really well.

Regards
Gary.