Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to calculate the interest of a mortgage
Hi,
How can I do an amortization schedule that gives me the interest as its being paid off? Is there a function that can help. Thanks in Advance Any help greatly appreciated Regards Janet |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to calculate the interest of a mortgage
Hi Janet,
There is a family of financial functions that address this and similar problems: RATE(), NPV(), FV(), PMT(), NPER() etc. In Analysis Toolpak (ToolsAdd-ins, check Analysis Toolpak if not done already) there is a family for irregular payments. See HELP for details; post again in this thread if you still have problems. -- Kind regards, Niek Otten "Grd" wrote in message ... Hi, How can I do an amortization schedule that gives me the interest as its being paid off? Is there a function that can help. Thanks in Advance Any help greatly appreciated Regards Janet |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to calculate the interest of a mortgage
Hi,
Use IPMT function. If you don't have the toolpack installed, you can use the following formula: The interest due in the Nth month is, L*R*(1+R/1200)^(N-1) - P*((1+R/1200)^(N-1)-1) where L is the initial loan amount, R is the annual percent interest rate, and P is the monthly payment made. When you enter the formula in Excel, reference the cells containing the appropriate information for L, R, N, and P (or hardwire their values), and remember to start the formula with an "=" sign. Regards, B. R. Ramachandran "Grd" wrote: Hi, How can I do an amortization schedule that gives me the interest as its being paid off? Is there a function that can help. Thanks in Advance Any help greatly appreciated Regards Janet |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to calculate the interest of a mortgage
"Grd" wrote:
How can I do an amortization schedule that gives me the interest as its being paid off? Is there a function that can help. You are asking two very different questions. Some people have tried to answer the second question. The first question cannot be answered completely because you fail to state the frequency of the amortization that you want in your table -- for example, per payment or annually. In any case, you might want to look at Mortgage Amortization template. It might provide you with some good ideas, if it is not in fact exactly what you are looking for. One way to find the Mortgate Amortization template .... First, be sure that your Help search mode is set for online by doing the following. Click Help and Microsoft Help, then click Online Content Settings under See Also. If Show Content from Microsoft Office Online is not check-marked, click to add a check-mark, click Okay and exit and restart Excel. Then, click Help and Microsoft Help, then click the Excel Help toolbar and click Search Results. Under Search, select Template from the pull-down list, type "mortgage" in the search window, and press Enter. Scroll through the search results and select Mortgage Amortization Schedule. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to calculate the interest of a mortgage
Hi,
Thanks I located this and its very helpful Janet " wrote: "Grd" wrote: How can I do an amortization schedule that gives me the interest as its being paid off? Is there a function that can help. You are asking two very different questions. Some people have tried to answer the second question. The first question cannot be answered completely because you fail to state the frequency of the amortization that you want in your table -- for example, per payment or annually. In any case, you might want to look at Mortgage Amortization template. It might provide you with some good ideas, if it is not in fact exactly what you are looking for. One way to find the Mortgate Amortization template .... First, be sure that your Help search mode is set for online by doing the following. Click Help and Microsoft Help, then click Online Content Settings under See Also. If Show Content from Microsoft Office Online is not check-marked, click to add a check-mark, click Okay and exit and restart Excel. Then, click Help and Microsoft Help, then click the Excel Help toolbar and click Search Results. Under Search, select Template from the pull-down list, type "mortgage" in the search window, and press Enter. Scroll through the search results and select Mortgage Amortization Schedule. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to calculate the interest of a mortgage
Thanks
The IPMT function looks like the candidate. I will experiment with it. Janet "B. R.Ramachandran" wrote: Hi, Use IPMT function. If you don't have the toolpack installed, you can use the following formula: The interest due in the Nth month is, L*R*(1+R/1200)^(N-1) - P*((1+R/1200)^(N-1)-1) where L is the initial loan amount, R is the annual percent interest rate, and P is the monthly payment made. When you enter the formula in Excel, reference the cells containing the appropriate information for L, R, N, and P (or hardwire their values), and remember to start the formula with an "=" sign. Regards, B. R. Ramachandran "Grd" wrote: Hi, How can I do an amortization schedule that gives me the interest as its being paid off? Is there a function that can help. Thanks in Advance Any help greatly appreciated Regards Janet |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to calculate the interest of a mortgage
Thanks for pointing me in the right direction
J "Niek Otten" wrote: Hi Janet, There is a family of financial functions that address this and similar problems: RATE(), NPV(), FV(), PMT(), NPER() etc. In Analysis Toolpak (ToolsAdd-ins, check Analysis Toolpak if not done already) there is a family for irregular payments. See HELP for details; post again in this thread if you still have problems. -- Kind regards, Niek Otten "Grd" wrote in message ... Hi, How can I do an amortization schedule that gives me the interest as its being paid off? Is there a function that can help. Thanks in Advance Any help greatly appreciated Regards Janet |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Function to calculate the interest of a mortgage
Actually none of the functions will produce an amortization schedule.
They can be used to produce one, but the easy way is to download one from he http://office.microsoft.com/en-us/te...CT011377171033 Pieter Vandenberg Grd wrote: : Thanks for pointing me in the right direction : J : "Niek Otten" wrote: : Hi Janet, : : There is a family of financial functions that address this and similar : problems: RATE(), NPV(), FV(), PMT(), NPER() etc. : In Analysis Toolpak (ToolsAdd-ins, check Analysis Toolpak if not done : already) there is a family for irregular payments. : See HELP for details; post again in this thread if you still have problems. : : -- : Kind regards, : : Niek Otten : : "Grd" wrote in message : ... : Hi, : : How can I do an amortization schedule that gives me the interest as its : being paid off? : : Is there a function that can help. : : Thanks in Advance : Any help greatly appreciated : : Regards : Janet : : : |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want the PMT function to calculate using 360 days not 365 | Excel Worksheet Functions | |||
how to calculate quarterly compounded interest in ms excel | Excel Discussion (Misc queries) | |||
calculate odd days interest | Excel Worksheet Functions | |||
how do I calculate the annual interest when I know the compound | Excel Worksheet Functions | |||
Excel option to store trendline's coefficients in cells for use | Charts and Charting in Excel |