Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Calculate Number of Payments Remaining

I'm a bit new to finance, and I'm trying to figure out how to setup amortization schedules for the company I work for.

How can I calculate the number of payments (monthly) remaining dynamically, given the start date in a cell, the total number of payments in another cell, and today's date in a third cell using =TODAY()?

I did tons of searches on Google and Yahoo!, but everyone wants money to provide the answer. I know it's a simple task, as a friend whom works as a QA Engineer at Microsoft - Excel division told me it's not that hard. I read through the date functions here:

http://office.microsoft.com/en-us/ex...me%20functions

Still trying to figure it all out in my head. It's funny that I studied tons of computer languages and finance, but I can't figure out this one simple little piece of logic.

Please note that since these are amortized leases, the information will not be the same using simple PV/FV/Annuity formulae, as the payments remain fixed.

Thanks in advance for any help!


Post your answer or comment

comments powered by Disqus
Hello,

I am trying to use the function NPER to calculate the number of payments it would take to eliminate some debt using a few constants.

In the NPER formula, I have the following
RATE = 12%/12 (12% APR, 12 months in a year)
PMT = 60
Pv = 3000
Fv = 0

The results of the formula = -40.7

However, when evaluating it by creating an amortization table, the number of periods requires 70 months to pay the debt off. The way I created the amortization table is by taking:
Debt = A1
Rate = B1
Payment = C1
Cell A2 = A1+A1*B$1$/12-C$1$
Cell A3 = A2+A2*B$1$/12-C$1$
Cell A3 is copied down to A4, A5, A6, A7, and so forth until the last cell has a starting balance of 0 or negative.

Is my amortization table correct, or is NPER populated wrong?

Thanks for any help you can give me.

Alex in Memphis, TN

Hi there,

I've set up a spreadsheet for someone else that calculates the number of
days worked by temporary employees. When I initially set it up, I thought I
just needed a column for start dates and a column for end dates, but the
person I have set it up for also wants a number of dates worked up to now
column.

I'm very new to this so realise that the way I have done it is probably
quite clumsy.

My original formula in C (days worked) was
=IF(OR(ISBLANK(A1),(ISBLANK(B1))),"",(B1-A1)) where A=start date and b=end
date. I used isblank so that C would remain empty if A and B were blank.

My problem is how do I amend this formula so that if I added an extra column
(today's date) C(days worked) would say stay blank if A is empty if B(end
date) is empty calculate number of days worked so far but if B(end date) has
a date then calculate how many day worked altogether.

Hope this isn't total gobbledygook.

Many thanks
Mifty

--
Mifty

Hi there,

I've set up a spreadsheet for someone else that calculates the number of
days worked by temporary employees. When I initially set it up, I thought I
just needed a column for start dates and a column for end dates, but the
person I have set it up for also wants a number of dates worked up to now
column.

I'm very new to this so realise that the way I have done it is probably
quite clumsy.

My original formula in C (days worked) was
=IF(OR(ISBLANK(A1),(ISBLANK(B1))),"",(B1-A1)) where A=start date and b=end
date. I used isblank so that C would remain empty if A and B were blank.

My problem is how do I amend this formula so that if I added an extra column
(today's date) C(days worked) would say stay blank if A is empty if B(end
date) is empty calculate number of days worked so far but if B(end date) has
a date then calculate how many day worked altogether.

Hope this isn't total gobbledygook.

Many thanks
Mifty

--
Mifty

Ok I have a spreadsheet that pulls a report of date an employee worked, name and hours. I have a table where it pulls and totals the hours worked for each month for that specific employee. This gives me the actual hours worked and then I have another table that is used for projecting out each month and gives me a total of hours that will be worked for each month. What I want to do is have a calucation that will calculate the number of days remaining in the month from todays date, and multiply that time 10 hours (this will give the total remaining hours to be worked) and then I want the Actual worked hours subtracted from the remaining days in that month hours. Of course if the month is passed then the total remaining hours for that month should be zero.

I have tried
= IF(NETWORKDAYS(TODAY(),G11,Holidays)>0,,"0")
but I know that is not right. Please any help would greatly be appreciated.

Hi i am working on a spreadsheet that should calculate the number of months remaining on a lease, or the number of months that have passed this is the information i have available

and what would be entered into the sheet
example?
LEASE A
start date 12/26/2009
Term : 44 months

I would like something that will tell me the number of months remaining on this lease , according to my calculations there should be 30 months remaining . what is the easist way to do this and just drag down the formula

further more, if the lease is expired or has less than 6 months left i would like it to return "expired"

Any help would be appreciated~ thanks

Hi

I have uploaded a sample amortization schedule. Pls refer to that.
1. I require the table to adjust itself based on the loan period and number of payments per year entered in D14 and D15 respectively.

2. Also, if a value is entered in column E, then i require the whole table to update as well.

thanking you
Suju

I'm trying to calculate number of years from the difference of two dates.

For example:
Column A = 8/18/1997
Column B = NOW() which is displaying the current date ie 07/10/08
Column C = the number of years based on a formula which I don't know how to write

Any suggestions.

i need to calculate number of rows with data in a particular column.
whether the number of rows filled (of column A) with data is 3 or more.
msg hello is printed.
is this syntax correct?

Code:
   

 If workbook2.Sheets("Sheet1").CountA(A) = 3 Then
  Msgbox "Hello"


have 2 inputs, start and end date. i would like to calculate number of
weekdays (have manually entered my desired results below).
have googled the last hour and cannot find the formula i need. btw i
will be sending this spreadsheet to people without the analysis
toolpack so cannot use networkdays function.

start date end date # working days
Sun 01 May Sun 01 May 0
Sun 01 May Mon 02 May 1
Sun 01 May Tue 03 May 2
Sun 01 May Wed 04 May 3
Sun 01 May Thu 05 May 4
Sun 01 May Fri 06 May 5
Sun 01 May Sat 07 May 5
Sun 01 May Sun 08 May 5
Sun 01 May Mon 09 May 6
Sun 01 May Tue 10 May 7
Sun 01 May Wed 11 May 8
Sun 01 May Thu 12 May 9
Sun 01 May Fri 13 May 10
Sun 01 May Sat 14 May 10
Sun 01 May Sun 15 May 10
Sun 01 May Mon 16 May 11
Sun 01 May Tue 17 May 12
Sun 01 May Wed 18 May 13
Sun 01 May Thu 19 May 14
Sun 01 May Fri 20 May 15
Sun 01 May Sat 21 May 16
Sun 01 May Sun 22 May 16

Hi to master gurus in excel,

I really need your help to solve one issue: how to calculate number of
collor cells?

For instance, from cells range A2:F2 (6 cells),if there are 2 cells filled
in any colors, the counter in cell G2 should be equal to 4(6-2).

Any thoughts?
--
Thanks in advance!

I am trying to calculate number of customers who arrive every hour in a
day for theentire year.

suppose: the computer records the name and info of a person arrived and
stores the time he/she arrived like:

10:27 xyz
10:29 abc
10:45 123

so I want to calculate the total arrivals in an hour from 10:00-10:59
and I want to that for the entire 24 hour period and then for the
entire month for each hour,

Please let me know how could i do it in a faster way using exfel.

Thanks,
RP!

Hi Experts,
I have a query, I want to calculate number of days between 2 given
dates...to be more clear i want excel to calculate only business days
monday-friday and not saturdays and sundays. Please help me...

A column (A1:A60) is being progressively filled in with numbers, and occasionally some blank cells are between. I need a formula to work out the number of the remaining cells (that are yet to be filled in) from the last entry till the last cell (A60) of the column.
Any help will be much appreciated

Hi,

I want to calculate number of days between two dates, including start date also.
For Example
A B C (No.of days)
23/03/2012 30/03/2012 8
28/03/2012 28/03/2012 1

I dont want to use networkdays function, since it excludes weekends. (I want to include saturdays & sundays also)
right now I am using =(B2-A2)+1

Is it any other formula available.

the website says to calculate number of years type: =year(A3)-year(A2). when
i type this is i get a weird answer. problem is not that i get the number
signs....i get a date 1900. for example: 1/1/1900 0:00. please advise for
this is for homework, university. thank you to whomever responds.

D L Barnard

I am trying to Calculating number of days in leap year

B3=01/01/2012
B4=12/31/2012

=((MONTH(B4)&"/")&DAY(B4))-((MONTH(B3)&"/")&DAY(B3))

But output is 365 for 01/01/2012 – 12/31/2012

Should be 366 bez leap year

PS: MM/DD/YY format

Pls help …

Thanks !

I'm working on a plan to pay off my mortgage early. Each year I plan to
increase the amount I am paying by a fixed amount. For example, this year I
will pay $800. Starting next year I will pay $850, the year after that, $900,
and so on.

I know the NPER function to calculate the number of periods given a rate,
fixed payment, and present value.

How can I calculate the number of periods required to pay off the loan when
the payment is changing, assuming the timing and amount of the change are
known?

Thanks for your help.

Hello there,

Great website here, already borrowed one formula. Although now I have another obstacle to conquer.

The example:

Coloumn A contains dates format of 12/02/2009, but another format such as 10-Apr-09 etc could be used.

Coloumn B contains the amounts of payments received, i.e £5.00, £10.00, £20.00

Now what I require is to be display in another coloumn (say Coloumn C) the number of payments that were received last week and last month and then the total value of the payments.

So the sort of result I'm looking for would be like

Assume todays date is 19-04-09

A B C
12-04-09 £5.00 Last Week 4 Payments Value £45.00
12-04-09 £10.00
13-04-09 £10.00
14-04-09 £20.00

Thanks guys, any help greatly appreciated.

Hi all,

My company purchases certain items from vendors. The price for these purchases are fixed by contract. However, they occur on different dates. I am looking to forecast how much we will pay per month.

1) I would like Excel to calculate the number of payments made every month of each year in the dataset for every month from September 2010 to September 2050.
a) For example, to know how payments made September 2010, how many payments made Oct. 2010….all the way to how many payments made August 2050 and how many payments made September 2050.
b) I will not know the range of the data; it will change on every spreadsheet I receive. My supervisor does not want to manually calculate the range of the data; can excel calculate the entire range and then feed it into this formula for me?

2) I would like Excel to then sum how much would be paid each month.
a) If a certain vendor is paid for a month, we will pay the price equal to the “price” variable to that vendor.
b) I would like to sum the amount paid to all the vendors we purchase from every month.

Attached is an example worksheet showing the type of data I have. I showed the numbers I would like to be automatically summed. Let me know if this is unclear.

I am sure this is probably very easy; I am not very experienced in excel

Thank you very much.

GQuinn

Hi All,

I am using the below formula to calculate the number of days between dates, and it also calculates how many days to the current day, my only problem is if I type 24/04/12 in the column it shows as 2 days when in fact it is only 1 day, does anyone know how to modify to incorporate this.

=IF(D38"",NETWORKDAYS(B38,D38),NETWORKDAYS(B38,TODAY())) This is in Column E (Working Days)

A B C D E
Date of Meeting Master Release Reqested Time Received Working Days 06/01/12 24/04/12 12:00pm 2
Working days should read 1

Any help is greatly appreciated.

Shazz

Hi Ladies and Gentlemen, I humbly request assistance from an Excel 2003 expert.
I do enjoy a programming challenge and would love to be able to answer this one on my own but sadly, I cannot.

I have a workbook called 'May'.
It contains 31 worksheets entitled '1st', '2nd', '3rd etc - up to '29th', '30th' and '31st' and these correspond with the dates in that month.

The same row of each worksheet contains the names of employees. (About 18).
The user will manually complete the names in the appropriate row and columns of the '1st' worksheet.

I'm trying to find a macro/function/formula to copy that row of employees names to all the subsequent days (sheets), and ideally this would be activated by a user with a macro assigned button. (I can take care of the easy part - designing the button and assigning the macro).

The macro will initially be used on the 1st of the month to copy the names all the way through to the 31st. This is ideal if there is to be no change in the employees, but a user will need the facility to amend the list on any day throughout the month up right through to the end of the month.
(e.g. on the 14th of the month a member of staff leaves and needs to be removed from the list from the day after they left until the 31st. So.. The user amends the row of names to reflect the now absent staff member and uses the macro to re-populate the subsequent worksheets.
Then, on the 16th they are replaced and the new member of staff has to be added from 16th all the way through to the 31st)
My aim is to make the process as easy as possible for the user and to avoid as much repetition as possible.

The macro will need firstly to establish the remaining number of sheets from the date the amendment is made and then populate the appropriate rows of those sheets with the selected employees names.

I really hope I've made sense in my explanation and I'm very grateful to you for reading and considering.

So many thanks to you, Gordon.

Hi all

I would like to
1) check if the value in column g = "In Progress"
2) calculate the number of days the "In Progress" task has been opened using the date in column d based on company a, b or c from column f
3) return the count based on 3 different ranges (>0 and < 30, >30 and 60)

I am not sure if I can modify a sumproduct command something like

sumproduct(all company "a" with status "in progress" and calculate it as it goes)

I currently have it set up this way
1. column I calculates the days the task has been opened

	VB:
	
(G8="In Progress",TODAY()-D8," ") 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
2. column k then breaks it into the 3 different headings

	VB:
	
0")))) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
3. sumproduct then calculates all the entries for the companies based on all the entries in column k

I have over 6000 entries so dont want to have to use formulas in every cell which slows it down etc.

I'm trying to create a spreadsheet that our Instructors can use to track the time that they're teaching, somewhat like a time sheet.

So, I created a drop down list for them to select their names, and a drop down list to select the programs that they taught.

I would like a formula that looks up whenever an Instructor teaches and can calculate the number of hours they taught each class. And, the total number of hours they taught.
Ex.

A1 B1 C1
Bugs PALS 3 hours
Harry PALS 6 hours
Sally BLS 4 hours
Jim BLS 5 hours
Jim ACLS 2 hours
Sally BLS 3 hours
Sally PALS 1 hour

So, based on above, the formula would be able to calculate that Sally taught 1 hour of PALS. Sally taught 7 hours of BLS. And, in total she taught 8 hours.

Is there anyway that I can do this?

hello

first can i know or calculate the number of rows used in sheet. for exa: i have A1:A100 i want that B1=100 and if i delete some of rows the number of rows in B1 auto-change. and can let B2 display the number of rows deleted.

i have 2 sheets now, can the Cell C1 in sheet 2 display the number of rows in the sheets, B1 in sheet1 + B1 in sheet 2

tnx,


No luck finding an answer? You could always try Google.