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
0
Utf
12/31/2009 1:56:01 AM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1919 Views

Similar Articles

[PageSpeed] 14

"Gervis" wrote:
> I need an amortization schedule that allows
> changing the monthly payment amount.

Off-hand, I don't know of a free template that will do this.  But it is very 
easy to roll your own.  (Most MS templates do it wrong anyway.)

If you like, I could probably whip up a bare-bones spreadsheet to get you 
started.  If you are interested, send email with the same subject to joeu2004 
"at" hotmail.com.

The following are the basic formulas, although they could be embellished to 
handle details properly (e.g. the last payment, and early loan termination).

B1, loan amount
B2, annual interest rate
B3, number of payments (loan term)
B4, number of payments per year
B5, periodic interest rate:  =B2/B4
B6, normal periodic payment:  =ROUNDUP(PMT(B5, B3, -B1),2)

B9, loan origination date
F9, initial balance:  =B1

A10, payment number:  1
B10, payment date:  =EDATE($B$9,ROW()-ROW($B$9))
C10, payment due:  =$B$6
D10, interest amount:  =F9*$B$5
E10, principal amount:  =C10-D10
F10, remaining balance:  =F9-E10

A11:  =A10+1
B11:  =EDATE($B$9,ROW()-ROW($B$9))
C11:  =C10
D11:  =F10*$B$5
E11:  =C11-D11
F11:  =F10-E11

Copy A11:F11 down for the number of payments.

Whenever the payment changes, simply replace the formula in column C in the 
appropriate row.  It will be propagated down automatically.

The catch-up payment can be computed based on the remaining balance and 
remaining number of payments.  That's a detail (TBD).


----- original message -----

"Gervis" wrote:
> 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
0
Utf
12/31/2009 4:48:01 AM
Here's a free amortization schedule:
http://www.vertex42.com/ExcelTemplates/loan-amortization-schedule.html

Change the payment after two years; you can calculate it to be anything you 
want it to be.

-- 
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Joe User" wrote:

> "Gervis" wrote:
> > I need an amortization schedule that allows
> > changing the monthly payment amount.
> 
> Off-hand, I don't know of a free template that will do this.  But it is very 
> easy to roll your own.  (Most MS templates do it wrong anyway.)
> 
> If you like, I could probably whip up a bare-bones spreadsheet to get you 
> started.  If you are interested, send email with the same subject to joeu2004 
> "at" hotmail.com.
> 
> The following are the basic formulas, although they could be embellished to 
> handle details properly (e.g. the last payment, and early loan termination).
> 
> B1, loan amount
> B2, annual interest rate
> B3, number of payments (loan term)
> B4, number of payments per year
> B5, periodic interest rate:  =B2/B4
> B6, normal periodic payment:  =ROUNDUP(PMT(B5, B3, -B1),2)
> 
> B9, loan origination date
> F9, initial balance:  =B1
> 
> A10, payment number:  1
> B10, payment date:  =EDATE($B$9,ROW()-ROW($B$9))
> C10, payment due:  =$B$6
> D10, interest amount:  =F9*$B$5
> E10, principal amount:  =C10-D10
> F10, remaining balance:  =F9-E10
> 
> A11:  =A10+1
> B11:  =EDATE($B$9,ROW()-ROW($B$9))
> C11:  =C10
> D11:  =F10*$B$5
> E11:  =C11-D11
> F11:  =F10-E11
> 
> Copy A11:F11 down for the number of payments.
> 
> Whenever the payment changes, simply replace the formula in column C in the 
> appropriate row.  It will be propagated down automatically.
> 
> The catch-up payment can be computed based on the remaining balance and 
> remaining number of payments.  That's a detail (TBD).
> 
> 
> ----- original message -----
> 
> "Gervis" wrote:
> > 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
0
Utf
12/31/2009 5:26:01 PM
Reply:

Similar Artilces:

Upgrading Schedule + to Outlook
Does Microsoft have a Knowledge article on this process and what is the "Q" number. Or, does anyone have the instructions on how to perform this upgrade. Thanks, Joe Install Outlook - use File-Import-From another program-Schedule +. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After searching google.groups.com and finding no answer, Joe asked: | Does Microsoft have a Knowledge article on this process | and ...

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...

Need rotation schedule with 6 tables of 6 people for all to meet o
I am trying to develop an algorithm for a networking session. I need a rotation schedule so that everyone will get to meet everyone only once. I will have 6 tables of 6 people at each table for a total of 36 people. I have the tables lettered A B C D E F and 1-36 people numbered and want to rotate everyone through so they all get to talk to each other just once. On Fri, 13 Apr 2007, in microsoft.public.excel.charting, sccyclist <sccyclist@discussions.microsoft.com> said: >I am trying to develop an algorithm for a networking session. I need a >rotation schedule so that eve...

Scheduling campaigns
Newbie to MS CRM 3.0 and was trying to schedule campaigns to run on either a daily, monthly or weekly basis. Thanks in advance and apologize if I am posting to the wrong group. ...

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 ...

how to schedule automatic downloads of new mail
I previously used outlook express, and there was a box you could check to automatically download email from the server, and you could type in the time intervals, i.e. check for and download any new email every 5 minutes, 20 minute, 30 mintues, etc. I cannot find a similar feature in Outlook. Can someone explain to me how set up MS Outlook to schedule automatic downloads of new email from the server Only if you provide your version. -- Russ Valentine [MVP-Outlook] "sabsfilter" <sabsfilter@discussions.microsoft.com> wrote in message news:C8CE3CDE-BFCD-4ABC-9757-2CA652A0...

Accumulate Amortized Expenditures by month
I need some help with an excel sheet and you guys may be the ones to help. I have a list of capital expenditures that I want to amortize over X months. Lets say office equipment is $10,000 and we are amortizing it over 12 months starting 1/1/11. That gives us a monthly cost of $833.33 Total capital expenditures by month 1/1/11 - $833.33 2/1/11 - $833.33 3/1/11 - $833.33 4/1/11 - $833.33 5/1/11 - $833.33 6/1/11 - $833.33 7/1/11 - $833.33 8/1/11 - $833.33 9/1/11 - $833.33 10/1/11 - $833.33 11/1/11 - $833.33 12/1/11 - $833.33 So that is just one expense. No lets say in June we buy more compu...

recurring payments
I need to process some payments every month, and I understand that you can't set up a recurring batches with payments (I even tried to setup a recurring batches without payment, then when I entered payment amount, GPlains stops me saying "you can't add payment to that batch"). Any idea how can I process those payments more efficiently? Thank you Just thinking out loud, I wonder if using scheduled payments might help. -- Charles Allen, MVP "hlv" wrote: > I need to process some payments every month, and I understand that you > can't set up a recurr...

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...

Exchange Server unavailable for resource scheduling?
Hey guys, I was just trying to create a "shared resource mailbox" in Exchange 2003 SP2. The mailbox was created successfully. I am at the point where I go into "Calendar Options", in that new mailbox, and configure "Resource Scheduling", though when I click on it, I get a Microsoft Office Outlook popup... saying: "The connection to the Microsoft Exchange Server is unavailable. Outlook must be online or connected to complere this action." Strange thing is that I am online (connected) as I sent a test email to this mailbox from another computer and it ...

Scheduled personalized Reports for every user, is it possible?
Hi all, here is what I'am trying to do. I want to create a report that displays all open items (Quotes, Orders, Activities, Incidents...) for one user so if I open the report I'll see my open items and if Joe opens the report he's seeing his open items. No problem so far. I query the filetered views and get my data. But here comes the tricky part: Is it possible to schedule that report so every user of the systems gets a report of all his open items i.e. once a week? I do not want to create 20 reports with 20 different data sources, which would work. But I can not provide...

one table that shows purchase orders, cost accounts, invoices and payments
could anyone help us how to create one simple table in GP10 that combines all of the above without having to extract the data from multiple sources and then collating them, in Excel Hi, ur main requirement is extract the data for excel then u create a view andthen use this view to create smart list in GP. Using this smartlist u can view and and generate excel report. POP10100 – Purchase Order Work (header) POP10110 – Purchase Order Line Work (line detail) POP30100 – Purchase Order History (header) POP30110 – Purchase Order Line History (line detail) PM20000 – Open/Posted Transact...

Reporting Schedule error
Atter migrating from DPM 2007 to Server 2008 64bit- The reporting services Server cannont connect to the DPM database A single report does work, but can schedule anything? ...

PCCharge Payment Server, RMS timeout?
Hello, Having used the Knowledge Base description, I have attempted to configure RMS with PCCharge Payment Server. I have shared and can "see/write" with full permissions to the PCCharge folder and specified the path in Administrator (I've also mapped it as a network drive). The credit card tender type type specifies to use EDC. Finally, I have a sample user 6 set up on my PCCharge Server and the register is also called 6. I have been handpunching my credit card info (approval left blank, expiration date as 08/06), due to the fact that RMS doesn't recognize our current Ev...

How to tell when Bill Payment is done electronically vs. paper check?
Am I correct in thinking that if I pay a bill via an epay, and I never get a paper check in return from my bank, then the payee was paid electronically, i.e., the payment to the payee never involved any paper check anywhere through the process? I recently switched from MSN Bill Pay to my bank's bill pay service (I send payments from within M03 for both). There's a piece of information missing for me when I switched to using my bank as the bill payer: I don't have a list of 'participating billers' (such as on the MSN Bill Pay website). Unless I determine who the t...

Loan amortization #2
In the loan amortizaiton template, how do I change the settings so that the payments are calculated as being paid at year-end rather than at the beginning of the period? ...

Creat Schedule
Need to create 2 church schedules. 12 week schedul 6 Masses per wee One schedule is for readers - 1 per Mass = 6 per week One for list of eucharistic minister - List of Masses & people needed 4:30pm - 4, 7:00am - 3, 8:30am - 3, 10:00am 5, 11:30am - 5, 5:00pm - 6 The selection of people to serve are to be in random order Some people are both readers and Eucharistic Minister - so duplication of names at the same Mass needs to be avoided. Ex. Joe Smith is scheduled to read at 4:30 Mass and also to serve as a Eucharistic Minister at the same Mass. This can't be done I am not sure how to ...

Can I make a partial payment on an invoice?
Hi, Suppose that, last week, I received, entered, and posted an invoice from a vendor for $10,000. Now I want to print a check to pay them, but, for some reason, I only want to pay $5,000 right now. Is it possible to do that? I can pay more than the invoice amount by using the "Unapplied" field in the "Edit Payables Checks" window, but I can't figure out how to pay less. I guess one work-around would be to void the invoice and re-enter it as two $5,000 invoices, but I'd prefer not to have to do that if possible. thanks, David This is how we do it: Go to Tr...

Amortization calculator
I have a copy of Money 97 that had an amortization calculator included that I absolutely love. My copy of Money 97 will no longer run on my Windows Vista. Does anyone know of any products that have a amortization calculator similar to this one? In microsoft.public.money, shurbyz wrote: >I have a copy of Money 97 that had an amortization calculator included that I >absolutely love. My copy of Money 97 will no longer run on my Windows Vista. > Does anyone know of any products that have a amortization calculator similar >to this one? http://www.bankrate.com/brm/mortgage-ca...

amortization tables
how do i create amortization tables in Excel 2003 You can either create one yourself with Excel's financial functions; see http://office.microsoft.com/en-us/excel/HA011117451033.aspx or use one of many templates, like these: http://office.microsoft.com/en-us/templates/results.aspx?qu=amortization&av=TPL000 -- Kind regards, Niek Otten Microsoft MVP - Excel "Mpho" <Mpho@discussions.microsoft.com> wrote in message news:39A31A72-2793-402D-A1D1-FA45B6D64920@microsoft.com... | how do i create amortization tables in Excel 2003 ...

How future automatic payments are reported in the register.
Hi folks - I'm evaluating Money at the moment, and I've set up some automatic payments for future bills. Wildly easy and great. The only problem I have is that the two upcoming Apay events are being reported in my bank register as debits (in red, because my balance won't cover them yet) even though they are in the future. I understand that's it's probably trying to warn me that they're coming up and that I'll be overdrawn in the future, but the effect is that it's actually debiting the money before my bank does and giving me an artificial picture o...

AMORTIZATION
I WOULD LIKE TO GET A AMORTIZATION SCHEDULE. DOES WORKS (VERSION 7) ALLOW ME TO DO THIS. ANY HELP APPRECIATED crabby Please don't SHOUT. It hurts my eyes and is entirely unnecessary. If Works version 7 has a spreadsheet component, yes, it can be used to generate an amortization table. "CRABBY" <mcrabby@hotmail.com> wrote in message news:00dd01c3577d$e0c63340$a301280a@phx.gbl... > I WOULD LIKE TO GET A AMORTIZATION SCHEDULE. DOES WORKS > (VERSION 7) ALLOW ME TO DO THIS. ...

link employee start date to accrual schedule
Employees earn different accruals depending on their start date - It would be helpful is Great Plains looked at the start date and then updated to the propoer accrual schedule. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/...

loan amortization schedule #2
I am looking for a template to plug-in the payment, loan amount, interest rate and term for a loan to get a amortization table. Also if one of the four above are missing it will calculate the missing number. ...

mortgage payment
I've been trying to do a spreadsheet with a Princple and Interest Payment. I've tried the Pmt= formula builder and the payment come out about $200 higher than it should base on by tried and true HP12C. Is there an alternate formula that i can use? G On Mon, 13 Sep 2004 15:45:15 -0700, "Gordon" <gordonmkane@hotmail.com> wrote: >I've been trying to do a spreadsheet with a Princple and Interest Payment. >I've tried the Pmt= formula builder and the payment come out about $200 >higher than it should base on by tried and true HP12C. Is there an alternate ...