Fireswan asked:
I have an amortization spreadsheet and I need a cell at the top of the page that will tell me the remaining balance based on today’s date. Is there any way to get this? I have a basic understanding of using functions but if it is complicated I’ll need a detailed explanation.
http://1mortgagecalculator.info
I have an amortization spreadsheet and I need a cell at the top of the page that will tell me the remaining balance based on today’s date. Is there any way to get this? I have a basic understanding of using functions but if it is complicated I’ll need a detailed explanation.
http://1mortgagecalculator.info
You did not state which version of MS-Excel you are using, so I will assume it to be 2003 or earlier (though the same basic information is valid for 2007).
First of all, you may wish to get familiar with specific Functions that are used in such calculations:
• PMT
• PV
• RATE
• NPER
And this can be done by using the “fx” right beside the Formula Bar, and selecting any of the above Functions. Further details can then be seen by clicking the “Help on this function” link in the lower left-hand corner of the dialog window. (There will be examples as well.)
Another Function that is quite useful is the TODAY function (which has no parameter values to enter, just the open and close parenthesis). It will give you the current date of your computer.
One thing to understand, MS-Excel treats dates and times just like ordinary numbers. The way that a time or date is displayed on a worksheet depends on the Number Format applied to the cell. When you type a date or time that Excel recognizes, the cell’s format changes from the General number format to a built-in date or time format. By default, dates and times are right-aligned in a cell. If Excel cannot recognize the date or time format, the date or time is entered as text, which is left-aligned in the cell.
Dates/Times can be added, subtracted, and included in other calculations. So adding the value of “7” to a date would increase the date by one week (seven days). And adding “0.25” to a date/time would increase the current date/time by six hours, since 0.25 is one-fourth of a 24-hour day. To use two dates/times in a formula, enter the date/time as text enclosed in double-quotation marks, or as a reference to a cell containing a date/time value. For example, the following formulas would all display a difference of 68:
= “05/12/2006” – “03/05/2006”
= “05/12/2006” – B3 . . . . . . . ( where B3 = 03/05/2006 )
Once you have the data as you need it to be, and you have several cells with a running value, then you can produce your “Chart” using the Chart Wizard.
.