Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Financial formulae
Hello!
I have: A1 Value of loan B1 annual interest C1 60 Months (Period) D1 Loan amortization (to intercalate) E1 (Formulae to calc montly income of interest) F1 (Formulae to calc montly income of loan) G1 =Sum(E1+F1) Whe 1st income = 100% interest and 0% amortization; Last income = 0% interest and 100% amortization. Is it possible with excel formulae, please? Thanks in advance. an |
#2
|
|||
|
|||
On Thu, 10 Feb 2005 04:43:04 -0800, "an"
wrote: Hello! I have: A1 Value of loan B1 annual interest C1 60 Months (Period) D1 Loan amortization (to intercalate) E1 (Formulae to calc montly income of interest) F1 (Formulae to calc montly income of loan) G1 =Sum(E1+F1) Whe 1st income = 100% interest and 0% amortization; Last income = 0% interest and 100% amortization. Is it possible with excel formulae, please? Thanks in advance. an You need to be more clear. 1. Amortization usually means the total payment. But I don't know what "intercalate" (D1) means in this scenario, as it would seem to be identical to G1. 2. Since you are writing about income from the loan, I assume you are making the loan. Is that the case? 3. Although it is easy to calculate the first payment as "interest only" (=Value * annual_interest/12), the usual subsequent procedure, in the US, would be to then calculate a regular monthly payment (using the PMT function in Excel), which would pay off the loan after another 59 (or 60) payments. If there were a residual, it could be added to the last month's payment. If those assumptions of mine are correct, then the formulas are as follows: A1: Since this is money you are lending, enter it as a negative value D1: =G1 E1: =-$B$1/12*A1 F1: 0 G1: =E1+F1 A2: =A1+F1 D2: =G2 E2: =IPMT($B$1/12,ROW()-1,$C$1-1,$A$1) F2: =PPMT($B$1/12,ROW()-1,$C$1-1,$A$1) G2: =E2+F2 Select A2:G2 and fill down to row 60. Note that if you change the rows, you will need to adjust the function ROW()-1 to reflect the correct payment number. Depending on your initial values, there may be a few cents balance remaining after the final payment. That could be your final "income". --ron |
#3
|
|||
|
|||
Ok, RR.
Thanks for your reply. In fact, your help is the solution for my question. Many thanks. an -----Original Message----- On Thu, 10 Feb 2005 04:43:04 -0800, "an" wrote: Hello! I have: A1 Value of loan B1 annual interest C1 60 Months (Period) D1 Loan amortization (to intercalate) E1 (Formulae to calc montly income of interest) F1 (Formulae to calc montly income of loan) G1 =Sum(E1+F1) Whe 1st income = 100% interest and 0% amortization; Last income = 0% interest and 100% amortization. Is it possible with excel formulae, please? Thanks in advance. an You need to be more clear. 1. Amortization usually means the total payment. But I don't know what "intercalate" (D1) means in this scenario, as it would seem to be identical to G1. 2. Since you are writing about income from the loan, I assume you are making the loan. Is that the case? 3. Although it is easy to calculate the first payment as "interest only" (=Value * annual_interest/12), the usual subsequent procedure, in the US, would be to then calculate a regular monthly payment (using the PMT function in Excel), which would pay off the loan after another 59 (or 60) payments. If there were a residual, it could be added to the last month's payment. If those assumptions of mine are correct, then the formulas are as follows: A1: Since this is money you are lending, enter it as a negative value D1: =G1 E1: =-$B$1/12*A1 F1: 0 G1: =E1+F1 A2: =A1+F1 D2: =G2 E2: =IPMT($B$1/12,ROW()-1,$C$1-1,$A$1) F2: =PPMT($B$1/12,ROW()-1,$C$1-1,$A$1) G2: =E2+F2 Select A2:G2 and fill down to row 60. Note that if you change the rows, you will need to adjust the function ROW()-1 to reflect the correct payment number. Depending on your initial values, there may be a few cents balance remaining after the final payment. That could be your final "income". --ron . |
#4
|
|||
|
|||
On Thu, 10 Feb 2005 07:24:59 -0800, "an"
wrote: Ok, RR. Thanks for your reply. In fact, your help is the solution for my question. Many thanks. an Glad to hear that. Thank you for the feedback. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I fix a circular reference in a financial statement? | Excel Discussion (Misc queries) | |||
cell format for financial functions | Excel Discussion (Misc queries) | |||
vlookup change column index position - without changing formulae | Excel Worksheet Functions | |||
How do I change the "Project Type" in "law Firm Financial Analys.. | Excel Discussion (Misc queries) | |||
Where can I see VBA code for financial functions? | Excel Worksheet Functions |