PMT function algorithm

Hi,

Could anybody explain me in details how excel evaluates payment rate in PMT 
function please.



Thanx
gacu@poczta.onet.pl 


0
gacu (1)
10/23/2004 6:06:58 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
447 Views

Similar Articles

[PageSpeed] 45

It's the interest rate per period. If you specify the number of periods as
number of monthly, you need to use a monthly interest rate (APR/12).

On Sat, 23 Oct 2004 20:06:58 +0200, "Gacu" <gacu@poczta.onet.pl> wrote:

>Hi,
>
>Could anybody explain me in details how excel evaluates payment rate in PMT 
>function please.
>
>
>
>Thanx
>gacu@poczta.onet.pl 
>

0
anonymous (74722)
10/23/2004 6:13:16 PM
I believe the formula used is

(Principle * i * (1+i)^n) ) / ((1+i)^n -1)

i = period interest rate (annual rate/12)
n = number of periods







"Gacu" <gacu@poczta.onet.pl> wrote in message
news:cle6qq$cpo$1@nemesis.news.tpi.pl...
> Hi,
>
> Could anybody explain me in details how excel evaluates payment rate in
PMT
> function please.
>
>
>
> Thanx
> gacu@poczta.onet.pl
>
>


0
10/23/2004 6:22:04 PM
Your question is not very clear.  Myrna has defined the rate argument 
that you pass to the PMT function.  What Excel evaluates in the PMT 
function is the regular payment given rate, number of periods (nper), 
present value (pv), future value (fv), and type (0 if paments at end of 
period, 1 if payments at beginning of period).

Help for the PV function explains how.  The basic equation is

   pv*(1+rate)^nper + pmt(1+rate*type)*((1+rate)^nper-1)/rate +fv = 0

which is easily rearranged to solve for any variable except rate.  Rate 
must be solved numerically rather than explicitly.

Jerry

Gacu wrote:

> Hi,
> 
> Could anybody explain me in details how excel evaluates payment rate in PMT 
> function please.
> 
> 
> 
> Thanx
> gacu@poczta.onet.pl

0
post_a_reply (1395)
10/23/2004 6:38:50 PM
Reply:

Similar Artilces:

how can i use an external function while customizing onLoad?
hi.. my problem is that i want to use an external js to use in onLoad.. as i can see it can be done by using global js.. but i dont want to change the system file. is there any different way? For example i have found some functions using eval but i could not do it. thank you Soner Sener www.coretech.com.tr ...

Windows Update Destroying Access Functionality
I recently went through the automatic updates that were to be installed on my computer, and I found that one of them apparently had the sole function of destroying the ability to edit data in linked Excel tables. There didn't seem to be any rationale provided that this was fixing any other issues. Any idea about why we'd want to install such a destructive update? -Amy oh come on this is a patent lawsuit that Microsoft lost-- because they weren't willing to pay the patent holder a reasonable amount of money it's in South America; it specifically has to do with editin...

Exporting functions from my application
Hi, I know how to export functions from a DLL And I know how to use WM_COPYDATA to do a very simple IPC. But is it possible to export functions directly from the exe? What I am aiming to do is. 1- Start my main application 2- The main application would then start another exe 3- The 'other' exe would contact the main application to get needed information via some exposed API/Functions. Would the above be possible? Simon "Simon" <spambucket@example.com> ha scritto nel messaggio news:67lfdtF2p7l6uU1@mid.individual.net... > But is it possible to export functi...

Weeknum function #2
I've been creating a spreadsheet that takes figures from specific date and summarises into weeks and came accross this problem.. E.g. =WEEKNUM("20/9/2005") returns '39'.. it is however week 38! However, using dates from last year e.g. =weeknum("14/10/2004") it returns the correct week 42. Is it me or is this a bug?... i've tried looking for some sort of patc but no joy -- madhatter_scf ----------------------------------------------------------------------- madhatter_scfc's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2725 ...

Sum function not updating
The sum function in Excel has stopped updating. If I enter new data, the sum function will not calculate it, unless I retype the function again. For example: Volumes 10 15 20 25 I currently have the next cell reading "=sum(C2:C5)" This function should give me a total of 70. However, if I change one of the cells, like the first cell that contains 10 to 50, the function will not update. It shuold automatically update any calculations that are made in cells C2 to C5, correct? Why is Excel not doing this? The frustrating thing is that if I go back and retype the function ...

Help with Camera Function
I'm trying to copy a picture of part of a spreadsheet into a Word document. The Camera function found in Tools\Customise\Commands\Tools almost does the trick but I particularly want to include the Row and Column headings so it's easy to refer to particular cells or ranges. Can Camera be used to show Row and Column headings as well as cells and, if so, how? If not, what is the easiest way of achieving what I want to do? Thanks a lot Hi Try Snagit for this http://www.techsmith.com/ -- Regards Ron de Bruin http://www.rondebruin.nl "nospaminlich" <nospaminlich@di...

Sub or Function not defined
Long time XL97 user, now XL2003. I've used excel macros for years and don't remember having to do this: I have macros in my Personal.xls that I use in all my workbooks. If I get "Sub or Function not defined" error, I have to reference Personal.xls using Tools/References and pick it out. But Personal.xls is named "VBAProject(PERSONAL.XLS)" and my new workbook is named "VBAProject(Book1.xls)", so I get "Name conflicts with existing module, project, or object library". So I have to make my Personal.xls in a different project. I do this...

WMP Music view no longer functioning correctly
I'm running latest version WMP 12 on Windows 7. The general top-level view of all music in the library (ie view obtained by clicking Music in left hand menu column, rather than Artists, Album, Genre etc below) is no longer functioning. Specifically, it displays only a single album artwork and title on the left hand, and all tracks are listed continuously ie not differentiated by album as should be the case. Also, the column heads are no longer responsive (ie cannot click on them to select by Title, Contributing Artist etc). Other views (by Artist, Album etc) are functioni...

Function in Table
I have a question, can you set up a table to automatically add or subtract two fields in that table. I have two currency values and I want the one to subtract the one value from the other if it is higher than the one and if it is not then I want it to add. Is there anyway that it can happen, please let me know. Thanks In a word: No. However you can do this in a query, form, or report as needed. That's usually the best way to handle this. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Anthony" wrote: > I ...

Mail Merge Functionality in 3.0
I am told that customizing mail merge documents for MSCRM 1.2 is very limited. Example, you cannot add any desired additional fields on the mail merge template. My question is, in MSCRM 3.0 can this be done? Or do you still need to purchase a third party add on program? Thank you in advance for your help! ~Cayla hmmm sort of looks like you can use custom fields in everything apart from activities...I have not tried it yet...anyone care to comment? ========================= John O'Donnell Microsoft CRM MVP http://www.microsoft.com/businesssolutions/crm/community/microsoft_crm_co...

Install add-in with user defined functions 02-12-08
Hi, Looking for an easy way to make user defined functions available for any mdb to open in Access 2003. I thought: create and mda, write VBA code and use Tools - Add-in Manager. That doesn't work because of a USysRegInfo table ...? Do I realy have to go through that 'misery' or is a simpler / better way? Many thanks, Frans van Zelm ...

merge function from one spreadsheet to another
I have a spreadsheet with names and addresses and another spreadsheet that has to be sent to customers asking for various pieces of information. I need to merge the address spreadsheet with the 2nd spreadsheet in order to produce a copy for each customer. Is there a function in Excel where I can do this? I've done merges with Word and Excel many times but never using Excel as both the datasource and end product. I'm using Excel 2003. Thank you, Pat Pat, Do you want an electronic copy, or a physical copy (a printout)? HTH, Bernie MS Excel MVP "Pat" <pscaccetti@gmail.co...

Is there a RATE function for single sum?
Is there a built in function for calculating the interest rate for a single sum? IOW, is there a function to calculate i in PV =FV/(1+i)^n ? I can do the math to solve for i but I want to know if there is a function to do it for me. The RATE function is for annuities. NOMINAL and EFFCT each take the other as input. And there are several functions to calculate security yields given specific dates. But all I want is a function to calculate i for a single sum. Is there such a function? On Sat, 18 Aug 2007 13:10:09 -0700, "Dave" <davefrick@newsgroup.nospam> wrote: &g...

Hiding formula/functions
I received a spreadsheet that someone wants me to help them with. The vba code that runs when a button is clicked exports a range to a csv file. I entered the following formula into the first column so that it would enter a 1 only when something is entered into column N. This part works. =IF(N8<>"",1,"") However, when I know export the data it thinks it should export all 92 rows because it thinks the formula is part of the data. How do I tell the code to NOT include the formula when it searches for a blank row? Thanks, I am thinking there is...

Counting Workdays Function
When i try to call the fuction i get a error I am using CalcWorkDays([DateDiverted],[dtmEnd]) The error says The expression is typed incorrectly or it too comples to be evaluated Does anyone know what wrong this I am a beginner VB Function CalcWorkDays(DateDiverted As Date, dtmEnd As Date) As Integer 'Calculated the number of working days between two dates 'DateDiverted - the first day to include in the range 'dtmEnd - the last day to include in the range 'Returns the number of working days between the two dates 'Both dates are counted if they are working days Dim in...

Can I retain functions from a previous cell when inserting a new r
I have an Excel spreadsheet. I need to insert a row, but have a column with SUM functions going down each row. Can I insert the row and have the SUM function automatically entered in the new row created? Hi see: http://www.mvps.org/dmcritchie/excel/insrtrow.htm -- Regards Frank Kabel Frankfurt, Germany "Philobr" <Philobr@discussions.microsoft.com> schrieb im Newsbeitrag news:B5E5E5B1-C5EA-446A-A43F-017BB67F6773@microsoft.com... > I have an Excel spreadsheet. > I need to insert a row, but have a column with SUM functions going down each > row. > Can I insert the ...

scrolling functionality to edit box
Hello, My application consists of drawing a series of rectangles in the edit box. I'm currently having some problems adding both vertical and horizontal scrolling features to an edit box in a dialog. Though I have checked "Horizontal Scroll", "Auto HScroll", "Vertical Scroll" and "Auto VScroll" in the "Styles" tab of this edit box, no scrolling goes on when I draw to my edit box. When I do a paint, the drawing exceeds the width of the edit box and even paints up to the borders of the dialog. What's strange is if I type text into the e...

if then function
In an if then statement, how do I get the value to be a result for instance If b12>b13, then b12*5% hi, =if(condition, true, false) =if(B12>B13,B12*.05,something else) >-----Original Message----- >In an if then statement, how do I get the value to be a >result for instance If b12>b13, then b12*5% >. > >-----Original Message----- >In an if then statement, how do I get the value to be a >result for instance If b12>b13, then b12*5% >. > I had this problem which i eventually solved my slef using this =IF(C5>C6,C37,IF(C5<C6,C6-C5,IF(C5=0,C8+C...

random generate functions
yes, i have done a little functions here and there, but its been a while. i cant seems to remeber how to generate randomly from lets say 20 cells. i know its round(rand 1-20, or something i just cant remember.i'd appreciate if someone could help me out. thanks in advace your all great. Hi have a look at RANDBETWEEN -- Regards Frank Kabel Frankfurt, Germany "WMILLER" <anonymous@discussions.microsoft.com> schrieb im Newsbeitrag news:d4d601c439f9$10e0e2b0$a101280a@phx.gbl... > yes, i have done a little functions here and there, but > its been a while. i cant seems...

Country Functions
Country functions differ from country to country. Is there a list of functions. For example in Denmark the equivalents are 1. CHAR - CHR 2. AND - OG Thanks in advance KeepItCool has a translator utility at: http://members.chello.nl/keepitcool/download.html dl wrote: > > Country functions differ from country to country. Is there a list of > functions. For example in Denmark the equivalents are > > 1. CHAR - CHR > > 2. AND - OG > > Thanks in advance -- Dave Peterson ...

normsdist function maximum
I was just doing some investigation and found that the normsdist function errors when given a value higher than 2147483647. I worked on this when doing some calculations for options based on Black Scholes and I thought it might be helpful for others running into problems with the 1.#INF infinity code from Excel. jasontferr...@gmail.com wrote... >I was just doing some investigation and found that the normsdist >function errors when given a value higher than 2147483647. I worked >on this when doing some calculations for options based on Black >Scholes and I thought it might be he...

CHR() function not working in Excel 2003
I've written a VBA application in Excel 2002, which works correctly. This should work on all recent versions of Excel, so I've been testing it on Excel 2003. This includes the lines: Dim a as String a = "text" + Chr(34) On Excel 2003, I'm getting the error "Compile error: Can't find project or library", and the Chr function is highlighted. If it's helpful, I've tried commenting this line out and I also get the same error elsewhere when I use the Space() function. The rest of the code runs successfully. I've seen elsewhere that I should look ...

Function Rounding
Just wondering if there is a way to get '14,037,000' to 14,037 by using a formular You could simply divide by 1000. And if you have intentionaaly put the single quotes, then assuming your text '14,037,000' lies in cell A1, then use: =SUBSTITUTE(A1,"'","")/1000 Mangesh "Lost" <Lost@discussions.microsoft.com> wrote in message news:AF4B5AC0-CE16-40AB-9472-896C07802C47@microsoft.com... > Just wondering if there is a way to get '14,037,000' to 14,037 by using a > formular > If the 14037000 is in cell A1, then i...

More function than FLOOR() !
I have a problem for an excel formula. I want to convert 1 to 0, 2 to 0,...... ..., 10 to 0. And convert 11 to 10, 12 to 10,............, 20 to 10. I know the formula of FLOOR can have this function, however, it will convert 10 to 10, 20 to 20 which is not I want. What I want is to convert 10 to 0, 20 to 10, 30 to 20, 40 to 30. Therefore, how can I achieve this result by using Excel Formular? Many thanks, Wilchong -- Message posted via http://www.officekb.com If you won't have very small increments =FLOOR(A21-1/10^10,10) -- __________________________________ HTH Bob "w...

Countif function #7
Dear Sir, I like to count a range of column, but I get only field that <3 and >7. What is wrong in my formular? Example: Countif(D2:d450,"<3 and >9")???? Thanks, Malyka Hi! Try one of these: =COUNTIF(D2:D450,"<3")+COUNTIF(D2:D450,">9") =SUMPRODUCT(--(D2:D450<3)+(D2:D450>9)) Are you sure you have the criteria correct? <3 = less than 3 >9 = greater than 9 Biff >-----Original Message----- >Dear Sir, > >I like to count a range of column, but I get only field >that <3 and >7. What is wrong in my formular? ...