deducting a monthly payment

Hi  I would like to have a cell displaying a total outstanding  loa
balance and another cell displaying the monthly payment and every mont
on a set day the payment cell would deduct the total outstanding balanc
and also tell me how many payments i have left. Any help would be muc
appreiciated

--
alanle
-----------------------------------------------------------------------
alanled's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3094
View this thread: http://www.excelforum.com/showthread.php?threadid=50619

0
1/29/2006 9:17:49 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
475 Views

Similar Articles

[PageSpeed] 16

alanled

Witht the monthly payment amount in G1, for the total still owing try:

=IF(DAY(TODAY())>=15,1000-G1*DATEDIF(C1,TODAY(),"m"),1000)

This is with the original loan, (1,000),  hard coded into the formula.  If 
you want replace the 1000 with the cell reference of the original total.

For the number of payment try:

=INT(F5/G1)&" Payments"&IF(INT(F5/G1)*G1<>F5," and $"&MOD(F5,G1),"")

-- 
HTH

Sandy
sandymann2@mailinator.com
Replace@mailinator.com with @tiscali.co.uk


"alanled" <alanled.22f0ba_1138569602.667@excelforum-nospam.com> wrote in 
message news:alanled.22f0ba_1138569602.667@excelforum-nospam.com...
>
> Hi  I would like to have a cell displaying a total outstanding  loan
> balance and another cell displaying the monthly payment and every month
> on a set day the payment cell would deduct the total outstanding balance
> and also tell me how many payments i have left. Any help would be much
> appreiciated.
>
>
> -- 
> alanled
> ------------------------------------------------------------------------
> alanled's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=30949
> View this thread: http://www.excelforum.com/showthread.php?threadid=506197
> 


0
sandymann2 (1054)
1/29/2006 10:20:50 PM
Reply:

Similar Artilces:

Wire transfer transaction in Payables manual payment transaction
Under Payables Management -> Manual Payments there is a need from many (most) of our customers to make payments through wire transfers. This is not currently being supported and we are looking at some help in building this functionality. The difference of a wire transfer from the other manual payment types supported is that there are additional bank charges involved. This means that if we have to pay Supplier A with $1000.00 the bank will charge (lets say) $10.00 so we need to make a payment of $1010.00 for the amount of the checkbook but we must clearly show that $10.00 was taken up...

How to show the month of the referenced cell (containing a date)
If a cell holds the date "2/1/08", what formula can I use that will give me the following result: "Feb - 08" I tried =Month(a2)......but I just get the number of the month. Thanks. =TEXT(A2,"mmm - yy") Or simply =A2 with the cell custom formatted mmm - yy HTH. Best wishes Harald "Dave K" <fred.sheriff@gmail.com> skrev i melding news:165b03da-19ba-40b8-b9fe-77b17ef045b5@d45g2000hsc.googlegroups.com... > If a cell holds the date "2/1/08", what formula can I use that will > give me the following result: > > "Feb - 08&...

Still Confused About ecoring Online Payments
I just instlled Money 2007 and activated an online payment account. I cannot get online payments to automatically get recorded in the check register. I askeds about this here last wek and thge response I got was that if I made the payment through Money, it would get recorded. Here is what I am trying to do.... I get a bill in the mail from Acme Wigits for $34.95. I want to pay it using online bill pay. I start Money and select Account List/Checking/Make a Payment/Make an Electronic Payment. (I am still in Money according to the title bar). I then enter a payment of $34.95 to Acme Wigit...

Garnishment + Neg TSA deduction + Deduction in Arrears = Error
GP version 10 SP2 An employee has 1) a garnishment and 2) a negative TSA deduction (-$15) on a payrun. Everything is correct on the paycheck, including the neg TSA being added back into taxable income. The problem is the Deduction in Arrears (DIA) creates a record showing that the TSA deduction was $15.00 and will try to take it on a future payroll. There should not have been a DIA record created at all. ...

Bug in DRP with monthly payments??
I have an annoying problem with the debt reduction planner that has been around for at least a few versions. I am currently using v2006. If I go into the debt reduction planner, then select base my plan on: what I want to pay each month and enter anything other than the minimum required payment, I get some crazy results for the payment schedule chart. For example, it tells me I need at least $2500 for debt payments. If I put that amount in something larger, say $3000, then for the first month it applies $20,000 to debt and the next month like $10,000. Where does it come up with the...

Changing TSA settings for a deduction
We corrected the TSA settings for an insurance deduction in setup and rolled it down. When we look at the payroll cards for the employees, the roll down did not change any settings. When I try to change the "TSA Sheltered from" settings I get the following error. "Activity exists for this code. The tax status cannot be changed" In the payroll settings I also checked the edit financial fields option based on KB866519. This article is based on ver 6 and In version 8 I can't seem to delete the summary options. Is it possible to make this change? If so, then how? Tha...

Printing the whole month for my calendar
so I'm trying to figure out how to print my calendar in outlook 2003. My problem is that Jan has 31 days therefore this would be 6 weeks and I can't get it to work. I'd like to keep the weekend uncompressed. Any ideas? The help is appreciated! Try one of the Word Templates for printing your calendar here: http://www.slipstick.com/addins/calendar_print.asp -- 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 furio...

payroll deduction on net of only some deductions
Should be able to calculate a deduction as a percentage of gross wages minus taxes and any selected deductions. This setup should be available on a per deduction per employee basis. For instance, child support calculated on net wages except you can't include voluntary deductions in the calculation of net. ---------------- 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...

Convert financial week into corresponding month
Hi Probably a really easy one, but has me scratching my head. I have a column of numbers (week numbers) for the new financial year. What i would like to do is put in some formula or code or something in the adjoining cell that picks up the week number in the first cell and churns out the month, i.e. if 1 was in cell A1, April would be found in B1. It's not quite as easy as putting 4 weeks per month as some months are longer than others, but i still dunno. Needs to work on the date of the weeks i think. Any help?? Many thanks Rich On Mon, 23 May 2005 07:52:21 -0700, RichHough...

Monthly calendar in EXCEL?
My boss uses Publisher to make a monthly work schedule and she woul like to get another program to do it...she likes when it prints out t look like a calendar, with all the jobs for each day listed. Can EXCE make a calendar? Does anyone do this using EXCEL somehow? Than -- roger_hom ----------------------------------------------------------------------- roger_home's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1643 View this thread: http://www.excelforum.com/showthread.php?threadid=49126 roger Browse through the Excel Calendar templates at the MS Template G...

Sort date field by by months not years
I want to sort a date field and have it put in order by months, ignoring the year. Is this possible? Introduce an extra column. If your dates are in A, then B1 is =MONTH(A1) Copy down as far as needed. Now sort both columns, but use B as the sortkey -- Kind Regards, Niek Otten Microsoft MVP - Excel "kramsey" <kramsey@discussions.microsoft.com> wrote in message news:7C118C02-538C-4BB1-BEBD-79A880DE0BB2@microsoft.com... >I want to sort a date field and have it put in order by months, ignoring >the > year. Is this possible? The easiest way is to use and ...

HOW DO I DEDUCT A %
I am trying to create a price list subject to a discount. I need the formula in order to issue a nett price list Regards Les If you wanted to deduct a percentage from a price you could use something similar to the following formula. Say you have a price of £100 in cell B2 and you want to display this price minus 20% you would use the formula =B2-(B2/100*20). This would give you the value after subtracting 20% from the original, in this case £80. For different %'s just change the 20 figure in the brackets to whatever percentage you require. By using the cell reference ...

POS 2009 Recommended Signature Capture and Payment Terminals
I'm trying to find out what signature capture and customer facing payment terminals are being succesfully used with POS 2009. We were specifically looking at Veriphone MX870. My understanding is that Veriphone is currently testing this device wit POS 2009 but has not yet gave their blessing. I'm wondering if anyone is having success with this type of device and POS 2009. Anyway input would be greatly appreciated. I've had success with the Ingenico 6550. It must have a USB conection not RS232. It also requires the RBA load not UPOS. "simpletechllc" ...

Paroll deductions procedure.
Hi... We are on Dynamics GP 9.0 and we are having some deduction issues with one of our employees and i was just wondering if there is a way we can know how GP 9.0 performs deductions calculation? I mean, is there any document that explains how the deductions flow? or is there an option in the software that will let us with this information? Any help on this will be really appreciated. Thank you, Hetal. What problem are you having? The short answer is the Payroll manual is the only document that would have information. -- Charles Allen, MVP "Hetal" wrote: > Hi... > ...

Dates to Month only
Quick question... I have a list of dates mm/dd/yy and I need to pull the data into a table that counts all items in each month. How can I get the dates to = month? I know that I can change the formating, but it still reads it as a number. and will not correspond to my table heading. I would like to use this formula in my table: =countif(date,"January") The best way may be to pull the data into a pivot table, which will let you group by month: Say your data is in A1:B100, with A1 your date header ("Date") and B1 your data header ("SomeNumbers") select...

M2004 matching transactions to things months old.
I recently 'upgraded' from money 2000 to 2004. I had to export all my accounts in .qif format and import them in 2004. AFter dealing with a few duplicated transactions i thought i had things running smoothly. All i do is download my statements and manually mark them all reconciled. In money 2000, it would only ever ask me if a downloaded transaction matched one in the register if i had entered it manually. That was fine. However i'm finding with Money 2004, it's trying to match downloaded transactions against other reconsiled transactions that happened month or more ago....

Payroll Deduction Maximum Monthly deduction
Currently we can set a maximum deduction by pay period, yearly and lifetime. We would greatly appreciate having this setting for monthly as well. Our employees are paid weekly and we need to be able to put through a deduction once a month. Unfortunately we don't know in which week they will have a check. We are seasonal and they don't always get a check, but if they do have a check we need to pull out a specific amount monthly only once for that month. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. ...

Mail "Stuck" and not NDR'ed until half a month or more later...
problem is that our messages are not getting delivered and we're not hearing about it until months later.. ie. Your message did not reach some or all of the intended recipients. Subject: sometext Sent: 1/25/2007 11:16 AM The following recipient(s) could not be reached: bob@hisserver on 2/16/2007 3:36 PM Could not deliver the message in the time limit specified. Please retry or contact your administrator. <emailserver.domain.local #4.4.7> Note, it was "sent" 1/25/07 and today, we heard about it... Something's not rig...

Payroll
We have a traditional 401K and are starting to offer a Roth 401K. Does anyone have a suggestion of how to track two different types of 401k deductions so that an employee does not exceed the maximum combined amount for these two separate deductions? For example - 1 deduction 401k (before taxes) and another deducttion Roth 401K (after taxes) The combination of these two deductions cannot exceed a maximum limit per year. Thanks. Unfortunately, it's a manual process that can be supplemented with reports and customizations. You could create a business alert to notify someb...

Payroll deductions
We have a payroll deduction that will not post to the correct GL account, but posts to an error account, instead. I've checked the deduction, posting and employee set-ups -- all seem correct. What else could be causing this? Donna, When you went into Setup>>Posting>>Payroll Accts, did you also verify the department and position fields also? The G/L account is set up, but is the account marked "inactive"? Alana "Donna" wrote: > We have a payroll deduction that will not post to the > correct GL account, but posts to an error account, ...

Changing Mortgage/Loan payment from monthly to every other week
Using MS MONEY 2005 Deluxe, I've been successful in converting my car loan payment frequencies and values from monthly to every-other-week. However, when I try to do this with the loan attached to my mortgage, it fails to change. Trying to change it by editing the Bill series gives me the message: --------------------------- Microsoft Money --------------------------- This Recurring Payment doesn't match the default Loan Account payment, so changes you make here won't affect the default payment. If you want to change the default payment, click Cancel now, go to the Loan Acco...

Excel Formula for years and months
If the formula for number of years is =Year(latest date)-Year(earlier date) this formula only show the nubmer of years, and I would like it to show the number of years and months. I need help with this formula: > > > > A B C > > Year Year Service > > 01-Sep-89 31-Dec-09= 20 Years It should be 20 years 4 Months. (Remembering that teachers only work from September 1, to June 30, of each year a total of 194 days. From September 1, 1989 to June 30, 1990 = 1 Year of Service. I know it only 10 months, but t...

End of Month question
In a cell, I have the current date in format -dd/mm/yyyy-. In the cell below, I want to automatically insert the text Yes if this is the last day of the month or No if it isn't. What's the simplest way to do this? Thanks in advance. :) -- Purple_Jack ------------------------------------------------------------------------ Purple_Jack's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=23884 View this thread: http://www.excelforum.com/showthread.php?threadid=375192 Use: =IF(MONTH(DATE(YEAR(A1),MONTH(A1),DAY(A1))+1)=MONTH(A1),"No","Yes&q...

Deductions
It sure would be nice if there was a way in Money where I could track all of my deductions and see a report at tax time of a summary. Sure, I can use Deduction Pro or similar software, but I'd like to not have to do double entry for expenditures. The types of deductions I'm talking about are things like mileage, donations, etc. that do not necessarily have a debit associated with them. ---------------- 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...

Cannot Load Check Number in Payables Manual Payment Entry
I have encountered a strange scenerio. I have setup the checkbook with appropriate Cash Account and Next Check Number. When I make Manual Payment Entry, there is NO next check number being loaded after I chose the Checkbook ID. I have verified all my setting in Payables and Vendor and it is normal. I can load the check number correctly with other modules such as Print Payable Check and Bank Transaction. Do you have any suggestion on such strange case? Thank you. Hi, To be exact, I have captured the SQL statement generated by GP when I input the checkbook ID, hope someone can help me o...