Email:      Pass:    Pass?

Free Excel Forum

Net Present Value Calculation With 2 Different Payments

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello. I am struggling with calculating the net present value of payments with a 12% discount rate. The first 3 years (total of 36 payments) the payment is $950, and the subsequent 3 years (total of 36 payments) the payment is $450. The payments are made at the beginning of the year. I know from the book I am using the answer for this is $38,453 (they have it broken out that the first 36 payments NPV is $28,888 and the second 36 payments are $9,565). However, I am struggling how to do this within excel.

First, I determined I should use the PV function, as I needed to show there are payments at the beginning of the term vs. the end. Using the formula:
=PV(12%/12,36,950,1) I got an answer of $28,602.83, which is pretty close to the $28,888. However, I can't use this formula for the next 3 payments, as they are actually the 4th, 5th, and 6th payments.

I also tried doing a 'blended' rate of $700 (average of the 950 and 450 pmts over 6 years), =PV(12%/12,72,700,1) ,and came up with $35,805.76, but this is too far away from the $38,453, so I can't use this as a long-term solution.

Can anyone help? I've been searching the internet exhaustively and can't find a solution!



Similar Excel Video Tutorials

Helpful Excel Macros

Close a Workbook after a Time Limit is Reached
- This macro will automatically close the workbook in which it is located after that workbook has been opened for a certai
Determine if a Cell Contains a Function in Excel - Great for Conditional Formatting and Validation - UDF
- Determine if a cell in Excel contains a formula or function with this UDF (user defined function). This function return
Hide Formulas in a Worksheet and Prevent Deletion
- This macro will hide all formulas within a workbook and not allow them to be deleted. The page will not be protected li
Pop-Up Message Box When a Range of Cells Reaches a Certain Average
- This macro will display a message box when the numbers within a range of cells reaches an average of 5. The current mac
Return the ISO Standards Start of the Year in Excel - First Monday of the Year - UDF
- Return the date of the first Monday of any year with this UDF ISO Year function in Excel. This is a really simple and e

Similar Topics

I need a formula to help me with a present value calculation, I have a stream of payments as follows. There are 36 payments total, the first 6 payments are $100 and the final 30 are at $2,400 (see details below):

First six payments
Payments -- 6
Amount -- 100
Interest rate -- 10%

Last 30 payments
Payments -- 30
Amount -- 2,400
Interest rate -- 10%

Specifically I am having problems with the last 30 payments that start 6 period in the future.


Hello. I have another quandry related to excel 2007. I have to figure out the cash payments per year related to a 25-year lease term required for a $300,000 present value baseed on a 10% discount rate. The payments are in the beginning of the year. I was hoping to use the PMT function, but oddly enough, to calculate the payment it requires putting in a payment amount? If anyone has an idea on how to handle this, I would appreciate it. I know the answer is is $30,046 due to the book I have, but I need to understand how to do this myself.

Thank you very much!

I am looking at purchasing a portfolio of vehicle leases with known payment schedules and residuals. The payments on different vehicles vary from 3 months to 40 months in term and I can calculate the monthly total. When the vehicles come off lease, I can also calculate and include in the stream of payments the guaranteed residual values. The discount is fixed at 15%. Is there a way that I can calculate the NPV and IRR using a given reinvestment rate, say 3% and known payments throughout the contract and total value of the payments and payment dates?

Thank you for your help. I am new to this forum but want to extend my thanks for all and any support.

I am trying to put together a sheet which calculates "structured" payments on a loan, ie the calculation of payments when there are other irregular payments made during the loan term, eg nil repayments around Christmas, larger or smaller payments at a particular payment interval or intervals during the loan term.

If possible, can the option be included to calculate the initial payment either at the beginning or the end of the first payment interval, ie the calculation function "Type" would need to be included if possible. Also, how is this option included in the calculation of normal loans anyway???

How are the "normal" payments calculated when these "structured" payments are also to be considered? Can anyone help me with this or suggest where to look?

Any help would be much appreciated!


I need to calculate the present value of 360 uneven payments (30-year mortgage). Years one through five would be $1x, years six through 10 would be 1.5x, years 11 through 15 would be $1.8x, and so on.

How can I create a calculation that takes the future payments in cells A1 through A360 and discounts those back at a given discount rate?

This is very easy to do with a financial calculator, but I can't seem to figure it out in Excel. Any help is appreciated!


hey all!

first time poster here!!
strange question for somebody who can hopefully help me.

I'm trying to do an amortization schedule; which i've done no problems.
I've managed to allow for extra payments...

My problem is calculating payments, when there is 1 or 2 irregular payments..

For example..

$80,000 / 5 years / 10% / 0 RV
payments are ~1699.76 /mo

If the 5th payment, is a $10,000 payment, the regular payments become ~$1527.06/mo

my problem is formulating a pmt function to accomodate this.

any ideas?

I'm trying to calculate how many quarterly payments will fall in a defined fiscal year but can't figure out how to do it.

If I enter into a lease that starts on 1/1/07 I will have four payments in 2007. My payments are set up as quarterly in arrears so the first one will be due 4/1/07 the second will be due 7/1/07, the third 10/1/07 and the last on 1/1/08. Simple. Problem is, if I enter into a lease in some month other then January then I will end up not making 4 payments this year. I'd make at most 3 payments.

For cash flow purposes, I need to project into the future how many payments I will make on each lease each fiscal year. It will be four payments for each year other then the first and last year the lease is still active. To make matters worse, my fiscal year is 9/1 through 8/31 and leases are signed each month and generally with terms of 5 to 7 years.

The spreadsheet I use to track them includes a column for the first payment date, the last payment date (the last payment at the end of the lease term), the number of payments over the term, say 28 for a 7 year lease, and the payment amount per quarter. Then off to the right I simply fill in columns with a formula multipling the payment amount by the number of payments that will occure that fiscal year. One column per year 8 or so years into the future. Each column heading included the fiscal year i.e. "FY 2008".

I'm hoping someone has developed a formula that can determine how many payments will occure in each of the next 8 or so fiscal years based on the information I manually plug in now.


I have a list of properties and payments recording them in order received eg

Prop Payment
1 10
4 5
3 7
3 4
4 9
5 9
1 8
2 9

I then need a sum of all payments for each prop

Prop Total
1 (sum of all payments for Number 1 )
2 (sum of all payments for Number 2)
3 (sum of all payments for Number 3)
4 (sum of all payments for Number 4)
5 (sum of all payments for Number 5)

I have been trying for a while with no success. Any help is greatly appreciated.

Tom M

I understand that I probably want to use =rate . . . but running into some problems to solve the APR . . .

I'm going to give an example of a APR done by a computer program.

Total Money Borrowed: $200,000

Total Closing Costs (everything included): $5,000

Term: 30 Years (360) months

Rate: 7.5%

Quoting the example given:

To find the monthly payment for this loan, we can use $205,000 as the total loan amount (you are not borrowing this much but you will owe this amount when the loan is closed), 7.5% as the interest rate, and 360 as the number of payments (1 payment/month for 30 years). The monthly payment is found to be $1,433.39.

Since you are only borrowing $200,000 but paying $1443.39/month for 30 years, The answer is, Annual Percentage Rate = 7.75%.

Some other numbers that may help . . . (that I have in the spreadsheet)

Current % Rate on $200,000 7.500%
Current 1st Mtg PI $1,398.43

Current Monthly PI $1,398.50
Number of Payments 360
Total of Payments - $ Amount $503,461.45
Amount Financed $205,000.00
Finance Charge $298,461.45

Current % Rate on $205,000 7.500%
New Monthly 1st PI $1,433.39
Number of Payments 360
Total of Payments - $ Amount $516,020.31
Amount Financed $205,000.00
Finance Charge $311,020.31

I would like to use cell references rather than numbers

Thanks in advance,


Hi All! I am a new guy to the block and would appreciate some help concerning the following:

I need to calculate the proceeds of recurring payments at a fixed interest rate over a fixed term with one exception to the norm: Annually, the PAYMENTS grow bigger at a fixed rate (10% in the example).

It's like this:

Year 1: Payment: $1,200, interest 10%, term remaining: 20years.
Year2: Payment: $1,320 ($1,200 + 10%), interest still 10%, term remaining: 19 years of the initial 20.

The person will therefore be earning 10% compound interest on $1,200 for a full 20 years, 10% compound interest on $1,320 for 19 years, etc, until, in the last year, the payment will be $7,339.09. The person will earn 10% interest on that for 1 year only. What will the total payout be?

There has to be an easier way to calculate this than doing it the way I did here and hiding the calculations!

Hope to hear from you soon!



I'm a little confused by a PV calc I'm doing.

Yearly interest rate: 6.5%
Loan Amount: 22,000
Payment are made at beginning of yearly period
Loan is for 5 years.

So I have calculated using my PMT function that I need to make 5 payments of 4970.85.

The present value (i.e. PV) of these payments should be equal to the loan amount.

When I do the PV calc on the 5 payments

=PV(6.5%, 5, 4970.85, , 1)
this equals 22,000, which is what I expect

The problem I can't figure out is why the summation of the PV on each individual payment don't equal this exactly

Time (yr) Payment PV
0 4970.85 4970.85 =PV(0 * 6.5%, 1, 4970.85, 0)
1 4970.85 4667.47 =PV(1 * 6.5%, 1, 4970.85, 0)
2 4970.85 4398.99 =PV(2 * 6.5%, 1, 4970.85, 0)
3 4970.85 4159.71 =PV(3 * 6.5%, 1, 4970.85, 0)
4 4970.85 3945.12 =PV(4 * 6.5%, 1, 4970.85, 0)
TOTAL 22,142.14 ??????

Why don't the sum of the parts equal to the whole? I get 142.14 difference between the PV using 1 PV formula compared to summing the PV of 5 individual payments.

What am I doing wrong?



I have 1 colums with Payments due, and another with Payments recieved:

R 1 825.94----------------R 1 825.93
R 818.75------------------R 818.74
R 1 462.05----------------R 1 462.05
R 1 198.88----------------R 1 198.88

For theses its fine as the payment matches the exact amount due,

but for eg. the next 4 are

R 207.94
R 588.07
R 968.20
R 984.45

amd the payment is R2 748.65

And now I won't always know that the payment is for theses transactions, as they are not always consecutive, although they will always be closer together.

The list of Payments due can be very long, so trying to find a formua that will go through all the possible combinations will probably be impossible, but is there a formula that can find the payments if it knows they are restricted to being within a certain range of each other, but most likely they will be consecutive

I've searched but can not locate the solution to this little problem.

How does one calculate the present value of a series of payments that start in the future. Example: The interest rate is 11.25% and I am to receive 12 equal monthly payments of $77.02 starting 13 months from now. My initial thought was to run a PV for the 12 payments assuming immediate start, then run another PV on that amount for the 13 months till the payments begin. Am I approaching this correctly? How would I do this?

Thanks in advance for any and all replies.


The IRR formula is set up for annual payments. How can it be changed to
account for monthly payments? I know the NPV formula will work if you divide
the discount rate bay 12 for monthly payments. Do I multiply the solution by
12? I tried this and the result did not look correct. I do not want to use
the XIRR formula for this because it will be a template.

Thank you

Dear members, I am a new member and this is my first thread. I am not very good at excel. I want to make Loan amortizationh chart for my two mortgages.The amortization chart which I want to make which should show Loan information such as (1)Loan amount..(2)..Anual interest rate..(3)...Term of loan in years..(4).first payment date.(5)...payment frequency...fortnightly...)(6). fortnightly payment.....
This information is to be shown on the top of the page and then in the body of the page should have seven columns such as.(1).......No of payment.(2)...Payment due date fortnightly.(3) Total Payment(4)...........Additional payment...(5)..interest........(6).........Principal (7)(8)reducing balance.My idea is to see at a glance all the information in the chart for example if the loan is for 15years,then the fortnightly payment will be 390 payments and if I pay some additional payments then it should automatically calculate all the 390 payments. Is there anybody there who could help me please. Any help given to me will be very much appreciated.I am keen in fortnightly payments because I do payments fortnightly. Hope to hear from some one.I regret very much if I have caused any inconvience in this matter please. Regards.Prasadjam.

can someone please help me with the following issue.

Using Excel, build a table that shows what $100 will grow to in 1, 2, 3, ... 10 years, with an effective annual rates of 1%, 2%, ... 10%, and plot the results in a line chart.
Using Excel, build a formula to find the present value and future value of a growing annuity. Apply it to find the present value and the future value of a growing annuity where the initial payment in 1 year is $900, the payments grow after that for 9 more years (total of 10 payments) at 6%/year, and the discount rate is 12%.

Using Excel, create a spreadsheet which amortizes a 5 year loan of $30,000 with monthly payments at the end of each month, if the interest rate is a nominal annual rate of 10% with monthly compounding. The table should have the following headings: Payment#, Payment amount, Interest paid, Principal repaid, Balance outstanding.

In engineering economy problems we want to know the rate of return on an investment that returns a series of equal annual palyments for n years. This is a simpler problem than the IRR function is designed for, since it deals with varying payments which must be spelled out in an array.

I don't want to put the same annual payment in 20 cells to use IRR, and I want the number of years to be a variable, which IRR would not lend itself to. Rate should be able to do this task in a single cell, but the results I am getting are not accurate.

Basically I want to find the interest rate that will make the present value of the annual payments equal to the initial investment.

I need to calculate payments on a lease, but the payments are only due "in season" for this particular business, which means 6 months of the year. Interest should calculate on the non-payment months and be added to the ongoing balance. The lease is for 3 years (36 periods), but with only 18 payments.

Hey guys,
I've looked and looked and can't seem to find info on this anywhere.
I'm trying to calculate equal loan payments with an annual repayment.

However, the payments are always due on the same day (say March 15th of the following year) but the loan can be originated at any point in the year. So the time between the loan origination and the first payment most likely won't equal 1 year, but the rest of the payments will be a year apart.

So a loan is done today, August 11th 2008.
1st payment 3/15/09
2nd payment 3/15/10....

The equal payment thing is messing me up. I didn't see anything in the Excel help section....can any of you help? Thanks.

I have run into a frustrating circumstance with Excel and I hope that someone can give me a way around this, here it goes...

I have 3 variables:
Dollar Amount = $10,000
Equal payments over 5 years
Payments begin in year 3

I am building a real estate model, and here is the issue. My model is going to span 25 years, and this $10,000 payment will be paid off over a five 5 period, starting in year 3. All three of these variables are inputs, so they will change, however, I want a formula that will take the total amount, and spread that evenly over the 5 years starting in year 3. This information will run down a column, and the payment will be next to the corresponding year. The formulae needs to say: In year 3 Payments begin, therefore divide that payment amount of 10,000 over the next 5 periods, then stop. Visual Example:

2 1 -
3 2 -
4 3 2,000
5 4 2,000
6 5 2,000
7 6 2,000
8 7 2,000
9 8 -

Does anyone know a way to write this equation with out using multiple IF statements, such as, if(and(A4=3,10000/5,if(B4>0,B5=(10000/5),if(B5>0..

The way mentioned above will work, if the payments are over the course of only a few periods, but I would like an equation that can accomodate a change in payments from 5 years to say 25 years, with out having to re-write the whole formulae? Thoughts? Comments? Suggestions?

Thanks for the help.


I am struggling to find a way to count a number of consecutive non-zero values across 33 columns without resorting to a horrid nested-IF formula.

I'm using Excel 2003 and each row I have is an account (there are 5572 accounts). I have been tasked to check if a customer has made 6 consecutive payments (held on each row in columns K to AQ.

If a customer has made less than 6 consecutive payments then I need to multiply the total collected by 40%. If the customer has made more than 6 consecutive payments then I need to multiply the first six payments by 40% and then each subsequent payment (consecutive or otherwise) by 20% and total the figure.

Can anyone help?

I wanted to figure how much my savings would be on a loan if I made biweekly
payments instead of monthly payments. Example: if the regular monthly
payment was $1,231 , I would make two payments of $615 instead. How can I
determine how much I will save in interest with option 2?

loan amount:200,000
The above amounts are what I used to determine the monthly payment of $1231,
which multiplied out will have me repaying $443,160 over the life of the
loan. How much interest would I be saving making the biweekly payments is
what I want to figure out? Can anyone help?

I'm looking for a formula to help me determine how quickly I can pay down the remaining amount of my mortgage by making additional payments to the principal every two weeks.


Original mortgage amount - $195,000.00 (8/27/03)
Interest rate - 4.875%
Length of term - 20 years
Payment terms - Bi-weekly
Total # of payments - 455
Bi-weekly payment - $636.74 (principle +interest)
# of payments to date - 156
Current balance - $142,759.03

Question: How long will it take to pay off the full amount if an additional $100.00 ($200.00; $300.00; etc) is added to the principle every two weeks.

Ken Ellis

My understanding of how to do this in Excel is as follows...

-PMT(Interest,Number of Payments,Amount of Loan)

So that for a mortgage of 180k over 30 years at 6% the formula would read

PMT(6%/12,30*12,180000) giving monthly payments of 1,079.19

However, if I look at the BBC site - - it indicates the payment is 1,089.73

Am I doing something wrong or is there a reason for the discrepancy?

I am embarrassed to ask this question because it seems like it should be so simple, but I can't find anyone who can help me. Here are my cells: a=Current Balance, b=# payments, c=monthly payment, d=monthly payment (keyed in), e=check payment total, and f=final payment amt. I am trying to calculate a monthly payment by taking the current balance (a1) / # of payments (b1). The result is my monthly payment amt (c1). Since I know the monthly payments are not going to be 36 equal amounts, I want to calculate a final payment so I do a check by multiplying # of payments (b1) * monthly payment amt (c1). The problem is that the resulting answer is wrong, i.e. 78,290.44 (the orig current balance) instead of 78,290.28. However, if I set up a new column (d) and just key in the monthly payment amt (d1), and then multiply # of payments (b1) * the monthly payment amt I keyed in (d1), I get the correct answer. I can then run a final formula that calculates: =sum((a1- e1)+c1) and that gives me my final payment amt. Why can't I get a final payment amt by using a formula that incorporates a cell that has a formula in it -- why do I have to set up a separate cell into which I key in the monthly payment amt, then use that field to get my final payment amt?