Close Window   
Free Ebook
Got an Excel Question?
Ask it in our Excel forum!
TeachExcel.com
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



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!

Thanks!

Ursula

View Answers     

Similar Excel Tutorials

PMT Function - Get the Payment Due for a Loan in Excel
How to calculate the payment amount for a loan or similar financial instrument that has a series of constant paymen ...
RATE Function - Calculate an Interest Rate in Excel
The Rate function in Excel (RATE) calculates the interest rate for a financial transaction, such as for an annuity ...
NPER Function - Calculate Number of Periods Needed for a Set of Payments in Excel
How to calculate the number of periods required for an investment in order to get the desired return.  The number o ...
PV Function - Get the Present Value in Excel
The Present Value (PV) function in Excel will return the current value of an investment.  This calculates the curr ...

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.

Thanks


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!
Ursula


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!

Thanks


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!

Hedgeman50


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 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,



Barry


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!

Harry


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:

A B
1 YR PMT
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 -
etc...

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 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?

rate:6.25%/12
nper:12*30
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?



Hi,

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'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.

Specifics:

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 - http://www.bbc.co.uk/homes/property/...lculator.shtml - it indicates the payment is 1,089.73

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




Hello I have a problem column M is not calculating correctly. Their is a flaw in the formula because it calculates Final Payments dates month rather than using D2 month for the calculation. I am trying to calculate the amount paid for the current year, and how many payments have been made. I need it to calculate for future current and past payments. Please help with a fix for my formula. Be specific if you can because I have fiddled with it for a long time and I just cant find the answer... The worksheet is attached for reference and testing.
Thank you,
-Val

I want to calculate the number of payments that have been made on a mortgage between two dates (the first date being the first payment date and the other date being some random date in the future).

Payments are made monthly on the same day of the month. So, if the first payment date is 2/15/1999 then the next payment will be made on 3/15/1999, and the third payment on 4/15/1999, etc.

So basically I need a formula that counts how many times a particular day (determined by the day of first payment) occurs each month between two dates, e.g. between 5/15/2005 and 7/14//2006 the 15th day of the month occurs 14 times (including the first payment date).

Let's say I want to know how many payments were made between 2/1/1999 and 3/25/2011 (REMEMBER: payments are made on the 1st of the month in this case), what would the formula be for figuring that out???

NOTE:
A1: First Payment Date
A2: Second Date (any future date, not necessarily a payment date)

I've used, to no avail, =DATEIF(A1,A2,"m"). This function doesn't account for the fact that payments are made on the same day of each month. For example, with A1:1/1/2011 and A2: 3/31/2011 the formula returns 2 months even though 3 months of payments would have been made.

Can you produce a formula that counts the number of payments between two dates, recognizing that the day of first payment will be the day on which each following monthly payment is made???

THanks in advance.


hey all..

First time poster..
I feel i'm pretty confident with excel, though i am having this trouble.

I can calculate a repayment no problem, however i'm struggling with an irregular payment..

EG.
$15,000 @ 10% / 60 months = ~318'mo

that parts easy to calculate..
what i want to do is calculate REGULAR payments, if the 5th payment is higher..

EG

4 payments = regular
5th payment - $1000
55 payments = regular

I've tried doing it with am amortisation schedule.. but can't work it out.
any ideas?


I am giving a loan with a large initial disbursement, and then once certain contingencies are met, there are other disbursements. Payments won't start until next year, and then will be quarterly. (The assumption is that all the disbursements will happen before payments start.) I am trying to figure out the formula that will calculate the interest and the payments:

Total of $280,000
First disbursement: $150,000
Contingency One: $30k disbursement
Contingency Two: $20k
Contingency Three: $20k
Contingency Four: $20k
Contingency Five: $40k
The interest rate is 7%, accrued monthly.

Quarterly payments will start in March of 2011.

I know the date of the first disbursement, but not the dates of the subsequent disbursements.

Any help with be much appreciated. And, by the way, if you know of any way to do this, please don't hesitate to respond. Even an inelegant way is better than where I am today. Thank you!


I understand the PMT function returns the payment for a loan with either payments that start at the beginning of the period, or the end. However, is their a function that will calculate a loan payment based on a user defined number of upfront payments? Example - 18%, 100,000 loan, 60 months, with 2 payments due in advance.