Maths behind CUMIPMT function of MS Excel
-
hi all, Can anyone tell me the math behind this MS Excel functions. I am writing software for iPhone and I want to use the functionality like CUMIPMT function of Excel to calculate interest CUMIPMT(rate,nper,pv,start_period,end_period,type) Returns the cumulative interest paid on a loan between start_period and end_period. CUMIPMT(rate,nper,pv,start_period,end_period,type) Rate is the interest rate. Nper is the total number of payment periods. Pv is the present value. Start_period is the first period in the calculation. Payment periods are numbered beginning with 1. End_period is the last period in the calculation. Type is the timing of the payment. Type Timing 0 (zero) Payment at the end of the period 1 Payment at the beginning of the period
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
-
hi all, Can anyone tell me the math behind this MS Excel functions. I am writing software for iPhone and I want to use the functionality like CUMIPMT function of Excel to calculate interest CUMIPMT(rate,nper,pv,start_period,end_period,type) Returns the cumulative interest paid on a loan between start_period and end_period. CUMIPMT(rate,nper,pv,start_period,end_period,type) Rate is the interest rate. Nper is the total number of payment periods. Pv is the present value. Start_period is the first period in the calculation. Payment periods are numbered beginning with 1. End_period is the last period in the calculation. Type is the timing of the payment. Type Timing 0 (zero) Payment at the end of the period 1 Payment at the beginning of the period
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
This is an amortization schedule[^].
-
hi all, Can anyone tell me the math behind this MS Excel functions. I am writing software for iPhone and I want to use the functionality like CUMIPMT function of Excel to calculate interest CUMIPMT(rate,nper,pv,start_period,end_period,type) Returns the cumulative interest paid on a loan between start_period and end_period. CUMIPMT(rate,nper,pv,start_period,end_period,type) Rate is the interest rate. Nper is the total number of payment periods. Pv is the present value. Start_period is the first period in the calculation. Payment periods are numbered beginning with 1. End_period is the last period in the calculation. Type is the timing of the payment. Type Timing 0 (zero) Payment at the end of the period 1 Payment at the beginning of the period
Rupesh Kumar Swami Software Developer, Integrated Solution, Bikaner (India) My Company Award: Best VB.NET article of June 2008: Create Column Charts Using OWC11
If you happen to know the amount of the payment (assuming equal payments), there's a shortcut. Multiply the number of payments by the payment amount, then subtract the original balance. This takes a lot fewer calculations than summing all the individual interest amounts, and I suspect that might be important in a handheld device. If you don't know the payment amount, calculate it from: Pmt = Amt * (int / ((1 - (1 + int)^(-n)) where Amt = Initial Principal Balance Pmt = Payment amount each period int = Interest rate (in decimal form) for the period n = number of periods. The value of int can be calculated by dividing the annual interest rate by the number of payments per year. If you're assuming 10% per year, with monthly payments, int would be .10/12; if payments are made weekly, int = .10/52.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
-
If you happen to know the amount of the payment (assuming equal payments), there's a shortcut. Multiply the number of payments by the payment amount, then subtract the original balance. This takes a lot fewer calculations than summing all the individual interest amounts, and I suspect that might be important in a handheld device. If you don't know the payment amount, calculate it from: Pmt = Amt * (int / ((1 - (1 + int)^(-n)) where Amt = Initial Principal Balance Pmt = Payment amount each period int = Interest rate (in decimal form) for the period n = number of periods. The value of int can be calculated by dividing the annual interest rate by the number of payments per year. If you're assuming 10% per year, with monthly payments, int would be .10/12; if payments are made weekly, int = .10/52.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
That's only assuming your payments are going towards principle and interest equally. Most payments go preferentially towards the interest at the beginning of the mortgage. The amount that goes to principle depends on the interest rate.
-
That's only assuming your payments are going towards principle and interest equally. Most payments go preferentially towards the interest at the beginning of the mortgage. The amount that goes to principle depends on the interest rate.
No, that's fully amortized, with more interest at the start than the end. The (1 + i) factor is what does the trick, unless I made a typo above - I'll check that and correct it if I screwed up.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
-
No, that's fully amortized, with more interest at the start than the end. The (1 + i) factor is what does the trick, unless I made a typo above - I'll check that and correct it if I screwed up.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
Checked it again. Was my mistake - you're right.
-
Checked it again. Was my mistake - you're right.
I always have to look it up. You'd think, as often as I need it, I'd remember it. It must have something to with being old... :sigh:
"A Journey of a Thousand Rest Stops Begins with a Single Movement"