Amortization table problem

I am trying to set up a dynamic amortization schedule that allows me t
change the loan variables (interest rate, length of loan, amount, etc.
I am using the PPMT and IPMT functions to calculated the principal an
interest payments for each period.  My model covers a 30 year tim
horizon.   

The problem occurs when I set the loan time less than 30 years.  Fo
example, for a 20 year loan I have a line that counts 1, 2, 3, ..., 20
0, 0, to tell the PPMT and IPMT equations which period to calculate.
But when it reaches year 21, a zero feeds into the PPMT equation fo
time period, resulting in a #NUM! problem.  It works fine if the loa
lengths is 30 years.  But less than 30 and I get the error.

Is there any way to set this up so that a zero doesn't feed into th
PPMT and IPMT equations, while still retaining the dynamic function?  
don't want to hard code the number of years evertime I change the loa
length.

Thanks for the help.  This is driving me crazy

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

0
11/13/2003 3:24:46 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
232 Views

Similar Articles

[PageSpeed] 40

Try using an error trap.

If(iserror(your formula),"-",(your formula)

between the quotations you can place anything you want to 
see.  If you want a numeric value, omit the quotes
hth
>-----Original Message-----
>
>I am trying to set up a dynamic amortization schedule 
that allows me to
>change the loan variables (interest rate, length of 
loan, amount, etc.)
>I am using the PPMT and IPMT functions to calculated the 
principal and
>interest payments for each period.  My model covers a 30 
year time
>horizon.   
>
>The problem occurs when I set the loan time less than 30 
years.  For
>example, for a 20 year loan I have a line that counts 1, 
2, 3, ..., 20,
>0, 0, to tell the PPMT and IPMT equations which period 
to calculate. 
>But when it reaches year 21, a zero feeds into the PPMT 
equation for
>time period, resulting in a #NUM! problem.  It works 
fine if the loan
>lengths is 30 years.  But less than 30 and I get the 
error.
>
>Is there any way to set this up so that a zero doesn't 
feed into the
>PPMT and IPMT equations, while still retaining the 
dynamic function?  I
>don't want to hard code the number of years evertime I 
change the loan
>length.
>
>Thanks for the help.  This is driving me crazy.
>
>
>------------------------------------------------
>~~ Message posted from http://www.ExcelTip.com/
>~~View and post usenet messages directly from 
http://www.ExcelForum.com/
>
>.
>
0
anonymous (74725)
11/13/2003 3:53:17 PM
Reply:

Similar Artilces:

Upgrade to 2002 Problem
I recently used the Microsoft Site to upgrade my software from 2000 to 2002. Everything seemed to have gone well, except I now experience an anoying problem with Excel. Example: In a blank worksheet, I enter 500 in cell A1. In Cell B1, I enter "=A1/5" without the quotes, and the value displayed is 100. Now if I go to cell B1 and delete the leading "=" so that the cell value should be "A1/5", Excel generates a custom format "B2d-mmm" and the value shown on the formula bar is "3/8/2003", and the value shown in the cell is some apparent...

The number of columns for each row in a table value constructor must be the same.?
I have the following script that inserts rows into the table type and sends the table to the DeleteHeadlines stored procedure. 1. The rows listed below that are inserted into the parameter already exist in the table itself. 2. The stored procedure uses a merge to delete all of the rows if they exist in the source/target. 3. I get the following error on line 11: "The number of columns for each row in a table value constructor must be the same." 4. In order, the tables columns are HeadlineID,HeadlineTitle,HeadlineDescription,HeadlineContent,HeadlineStartDate,H...

pivot tables #5
I am trying to change the order in how the tables display. I don't want an accending or decending alpha order as it is set up now. I would like to pick and choose how I want them listed. How do I change the order? Select Item in Pivot table for which you want to change order, right click, select Field Settings, select Advanced, click "Manual" sort option, click OK; should be able to drag to arrange order. >-----Original Message----- >I am trying to change the order in how the tables >display. I don't want an accending or decending alpha >order as ...

Filer within Pivot Table
Hi, Using Windows 2000 and Excel 2000 I have created a pivot table which correctly shows the data returned, however, I don't want to show entries where the total (sum of data) is equal to 0 (a zero). I've tried to turn on Filter but this option isn't available. Any pointers most welcome. Rob Debra Dalgleish has written code for this http://groups.google.es/advanced_group_search?q=group:*excel*&scoring=d&lr=lang_en&hl=en search for hide zero value pivot table and in author put Debra Dalgleish -- Regards, Peo Sjoblom (No private emails please) "Ro...

Data Verification: Comparing Two Identical Tables in MS ACCESS
I am using Access for entering a large number of questionanaires and other tests all by hand and in order to reduce errors I am using manual first entry and second entry. I am looking for a way to compare the records in each table against one another and be able to view and edit the discrepancies. It would also be nice if there was a way to limit the results to a specific record. Does anyone have an idea on how it would be possible to make this happen in Access? I have tried the VBA approach using some complicated code I found but it is limiting and I would like to find a way to do it with mi...

Multiple inheritance problem
Hi, I want to make a class, JNDlg, which is derived from two MFC-classes (CDialog and CStringArray). The class declaration (a bit simplified) looks as follows: class JNDlg : public CDialog, public CStringArray { // Construction public: JNDlg(CWnd* pParent = NULL); // standard constructor // Dialog Data //{{AFX_DATA(JNDlg) enum { IDD = IDD_JNW }; //}}AFX_DATA //{{AFX_VIRTUAL(JNDlg) protected: virtual void DoDataExchange(CDataExchange* pDX); // DDX/DDV support //}}AFX_VIRTUAL protect...

Date ranges based on related table
Any tips on best way to do this. I have two tables, related one to many Case--Payments I need a query that finds the last payment for each case and outputs an X in one of three columns based on DateDiff('d',LastPayment,Date()) being 0-30 days, 31-90 days or over 90 days. -- Dorian "Give someone a fish and they eat for a day; teach someone to fish and they eat for a lifetime". UNTESTED UNTESTED UNTESTED SELECT Case.ID, (SELECT Max([PayDate]) FROM Payments WHERE Case.ID = Payments.ID) AS LastPayment, DateDiff('d', (SELECT Max(...

Problem while send/receive mails
Hi All, Iam an outlook user currently using outlook 2007, there is a problem while i am using outlook, when i click on send/receive it is downloading the mails but there is redundancy of mails that is it is downloading the same mails everytime i click on send/receive, i tried using archieve bit ON, but still gettin it plz help me Turn off your anti-virus integration with Outlook. -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious hea...

Tax table too big...
Upon setting the store operations up we have built a web site that goes along side it and our company has presence in 12 states, so our finance dept said we had to charge sales tax in those states by shipping zip code. This makes our tax table about 30k records or so. When the receipt at the pos is generating it takes about 45 seconds on this screen and then prints. If I delete 99% of these rows in the tax table, the rteceipt generates in a second or less. Is there a way to fix this wait time? TIA Okay fixed my own problem... It turns out it took forever on the receipt template po...

Active X Problems
I have an authorized copy of Office 2003. I went to the Office Homepage to take a training course. After being forwarded to help sections on Active X components , I followed all the requests. When I open "Manage Add Ons" , I can't find microscoft active x Add On to enable it Peter To change your Active X settings: from within Internet Explorer Tools>Interner Options>Security>Custom Level then scroll down and adjust the radio buttons to enable for active X controls to allow the Microsot site to download the training stuff. Hope this helps Jess "Peter F. Marge...

Amortization schedule with veriable payments
I need an amortization schedule that allows changing the monthly payment amount. I have a house that I have sold to someone with a contract for deed. I have an amortization schedule, but due to them having some financial probems they have lowered the monthly payment with my permission. That is why I would like to change the payment amount after 2 years of calculated payments. I would appreciate any ideas. Gervis "Gervis" wrote: > I need an amortization schedule that allows > changing the monthly payment amount. Off-hand, I don't know of a free template...

GP SQL Server Performance monitoring, problem with CPU related loc
The past week our GP SQL Server has been locking up. All GP clients (around 15) lockup and are not able to do anything within the app. When this happens the CPU on the server maxes out. It last for around 15 minutes then the CPU goes back to normal and everyone can start to use the GP clients again. Is there a way to see what is happening? For instance maybe its a batch/transaction/query/report etc. that a user is running that is causing this. Is there a log I can review or something to help me pinpoint what process is doing this? Thanks for your input! Hi, Can you pl...

Calculation in a form not entering table
Hi all. I have created a form (Orders Form) to enter some data regarding orders. This form saves the typed data into the table (Orders Table) including the customer's ID and Order ID. All nice and saved into the Orders Table, except from the Total field. Furthermore, I can type any kind of data into every field in the Orders Form, apart from the Total field where i have placed the calculation: =[Subtotal]*[VAT]/100+[Subtotal]. Doing this, i am able to view the total cost including the VAT. Later on, i would like to use the Orders Table for further data withdraw. But, without the tota...

Boot problem 03-04-10
I have a boot up problem with my xp pro. I shut my computer down at night and in the morning I get a grey screen at boot up, before anything shows. I can hear the computer going thru the startup process with nothing showing on the screen. After turning it on and off many times, including using a dos boot disc (which doesn't make a difference) it will finally boot. (a twenty-five minute process). Once it is running I am able to restart without any problem. It only happens after several hours of being shut down. I have run diagnostics on the hardware, monitor, graphics card and...

Problem with msflxgrd.ocx in vista
Hi I have an access 2007 database ive made in Windows XP that uses msflxgrd.ocx for some activex elements. The customer has Vista and Access 2007 but whenever I try to open the form that has the FlexGrid it gives me object errors. Ive copied the msflxgrd.ocx from the Windows XP install to the Vista install but it doesnt seem to work. If i try and create a new control I get a License Error. help! How did you register it? I'm just starting to tackle Vista problems, but I believe you need to register it as admin. It's also not in the list of Vista's supported vb6.0 run...

Pivot Table?
I need to be able to report on data within a spreadsheet and allow for this to be customisable by Month / Year. I thought that I could do this with a pivot table but i'm struggling. If I've basically got several rows containing the following data fields: Problem Ref:, Problem Description:, Date Raised: What I want to be able to do is @ the top level of the report - specify month / year raised, this will then filter all of the appropriate records underneath (all fields). Appreciate your help on this one - many thanks, Al Mackay ( mralmackay@aol.com ) So create your table, g...

CRM 3.0 Web Site Alert email problem
I installed CRM 30 SBE on our SBS Premium box and it installed fine. It seems to be functional too, even the reports. But i have been getting an email warning: Web Site Alert from SBS 2003 stating: "http://SERVER:5555/MSCRMServices request failed: Critical condition. HTTP Status: 400 Bad Request Response Time (msec): 0. (WMI Status: 0 )" It sends me the email every 3 minutes. I also tried accessing this URL and i get: HTTP Error 403 - Forbidden Internet Explorer error message stating: You might not have permission to view this directory or page using the credentials you su...

Send to look up problem
When sending an email I would like to only view contacts with email addresses and not ones with FAX also. What do I need to change in Outlook 2002 to make this work? See http://www.slipstick.com/contacts/nofax.htm Michael Servidio wrote: > When sending an email I would like to only view contacts > with email addresses and not ones with FAX also. What do > I need to change in Outlook 2002 to make this work? ...

How to get default values from a table
This seems like a simple matter but I am still having trouble. I have a form that is used to input customer personal data (frmCustInput). I also have a table which I acquired which lists city, state, county, area code, etc. for every zip code in the U.S (tblDistinctZipCodes). Zipcode is the primary key in this table. I want to be able to enter the zip code for a new customer in the form and have his city, state, and area code automatically populate their respective fields in the form. The data from the form is then saved to the customer table (tblCustomer). Hi Charles, > Zipcode is t...

view data from one table update and save to another table
I have a tabl do this without amending te with item data which I sow on a form to create a purchase order, where the item data may require amending before publishing to the supplier. How do I do this without amending original data which for audit purposes must remain as original. "Bloggsy" <Bloggsy@discussions.microsoft.com> wrote in message news:74D11113-3C3F-441F-8031-FA31035ED0B7@microsoft.com... >I have a tabl do this without amending te with item data which I sow on a > form to create a purchase order, where the item data may require amending > before publish...

table to text in word 2007
word 2003 had a table-to-text function where i could highlight a table and convert it to a tab delimited, etc text block word 2007 has text-to-table, but i cannot find table-to-text. how do convert a word table to a non-table block of text? With your cursor in the table it is in the data section at the right hand end of the layout tab. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< &...

Work orders problems
Hi, We use RMS (SQL 2000) in a retail environment as well as a POS environment, although this is obviously not recommended. In the POS environment, we use work orders to keep a "tab" of the client's bill until it is eventually tendered off. On a few occasions, we have had some weird things happen with work orders. On a few occasions, open work orders have managed to disappear whilst being saved. I.e. When the waiter adds more items to the work order and saves it, it just disappears. Other times, work orders have frozen up while the waiter was picking up the entire work ...

Exporting Ppivot table from Access to Excel
Hi there, (1) I am working with a huge Access database with 346,722 records (2) I created a pivot table in Access and that works (3) Now, I want to export this table to Excel (4) When I try to do this, it says "No Data" and returns a blank Excel page I understand that there is a limitation on the number of lines in Excel (~64K). But, since I have a massive database file, how can I possibly export the Pivot table to Excel? Any comments will be greatly appreciated! Thanks!!! rgds Kaushik You can build a series of queries based of the pivot that break the result down to manageab...

amortize
Will MS Money 03 amortize loans and manipulate the numbers? None of the boxes or the salespeople know. Money has a type of account, the Loan Account, for amortized loans. It has a type of transaction, the Loan Payment, that calculates interest expense and Principal Transfer components of each payment. That may or may not answer your question. "Asussertown" <DROPsussertown@adelphia.net> wrote in message news:eHGefFTSFHA.3336@TK2MSFTNGP10.phx.gbl... > Will MS Money 03 amortize loans and manipulate the numbers? None of the > boxes or the salespeople know. Money ...

Category axis problem
I use Excel 97. I have a chart based on two columns, one column ranges from 0 to 360 (catagery axis), the other has different values from 20 to 90 (value axis). I can't get the catagery axis to start from 0 (zero) It starts from 1, which is very disturbing. The problem starts with the menue "Axis lay-out". In the sub-menu Scale, the first field asks for: "Value axix (Y) crosses by category number: ? I would like it to cross at 0, but the systems insists that the number should at least be 1. Consequently, the labels at the category axis start at 1. Maybe the names of menues ...