Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and reporting in column A, where a series of column reaches zero
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and reporting in column A, where a series of column reaches zero
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
finding and reporting in column A, where a series of column reaches zero
Thanks Steve,
The Function worked really well. Regards Gary. -----Original Message----- 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" wrote in message ... 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. . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
finding the largest value for a name in a column and then returningthe result from a different column | Excel Worksheet Functions | |||
Finding a number in a column out of an array in another column | Excel Discussion (Misc queries) | |||
Finding Info from Column A and Removing it from Column B | Excel Discussion (Misc queries) | |||
Finding the closest number in column A and take the value in column B | Excel Worksheet Functions | |||
MIN/MAX functions and reporting from a different column | Excel Worksheet Functions |