• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel formula of installment dates of 5 years loan |General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Excel formula of installment dates of 5 years loan |General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Excel formula of installment dates …
sp_PrintTopic sp_TopicIcon
Excel formula of installment dates of 5 years loan
Avatar
Salim Gul
Member
Members
Level 0
Forum Posts: 37
Member Since:
December 24, 2018
sp_UserOfflineSmall Offline
1
March 3, 2020 - 4:13 pm
sp_Permalink sp_Print

Dear Sir

 

I want to set a financial formula of semi annual installments dates of 5 year ( the loan of 5 years semi annual return dates first date of installment is suppose 01-01-2016, second 01-07-2016, ........ till last date of installment 01-07-2020 automatically 

sp_AnswersTopicSeeAnswer See Answer
Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
March 3, 2020 - 8:08 pm
sp_Permalink sp_Print

Like this? (See attached)

You don't mention about bank holidays or if the calculation is every 6 months or based on 360 day/year or 365 day/year.

A straight 6 months is fairly easy

Purfleet

Avatar
Salim Gul
Member
Members
Level 0
Forum Posts: 37
Member Since:
December 24, 2018
sp_UserOfflineSmall Offline
3
March 4, 2020 - 1:41 am
sp_Permalink sp_Print

Dear Purfleet 

Thank you for guidance, but the formula which i want, there is only number of years, number of payment in a year and starting date is given all the colored dates set formula automatically 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
March 5, 2020 - 7:52 am
sp_Permalink sp_Print

Hello Salim,

Perhaps you are looking for the COUPNCD function?

Avatar
Salim Gul
Member
Members
Level 0
Forum Posts: 37
Member Since:
December 24, 2018
sp_UserOfflineSmall Offline
5
March 5, 2020 - 5:53 pm
sp_Permalink sp_Print

Dear Anders Sehlstedt

 

Yes you sir, but COUPNCD Formula or COUPNUM these formulas only show the dates of current year installment, but here i am looking for the formula which show the all dates of installments in 5 year from start date to last date of coupon (installment) 

one thing more i would like to add here, about what i am looking for, which is a formula of loan amortization named sheet in this forum, unfortunately i did not under stand how to formulate it 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
March 6, 2020 - 6:53 am
sp_Permalink sp_Print

Hello Salim,

Yes, the COUPNCD function gives you only the next coupon date after the settlement date, so you need to give it a new settlement date for each next coupon date. In attached file you will see what I mean. The end result is just the same as what Purfleet gave you. If you don't like this approach or want to present it differently, just go with whatever solution that works for you, for example using an IF formula to decide what date the settlement date is to be and so on.

The structure in the sample file is this:
Cell C3 = Number of years
Cell C4 = Number of installments per year
Cell C5 = Settlement date (Start)
Cell C6 = Maturity date (End)

In cell C8 I simply show the date in cell C5.
In cell C9 I use the =COUPNCD($C8;$C$6;$C$4), I have then dragged and copied this formula down to cell C17. The range from cell C8 to C17 now shows all the installment dates and as you see, the settlement date per row is the installment date from the row above. To my knowledge there is no other way to see all the dates at once, but if you for example just want to see in one cell when the next installment date is based on today's date, that can be arranged. A bit tricky perhaps, but doable.

For your other question about loan amortization, I assume you are looking for the PMT function. Check out these pages for information about it.
https://www.myonlinetraininghu.....t-function

https://support.office.com/en-.....#038;ad=US

Sometimes I find a good help in using a ready made template to better understand how to use the functions.
https://templates.office.com/e.....ortization

I hope I was able to give you some help.

Br,
Anders

sp_AnswersTopicAnswer
Answers Post

The following users say thank you to Anders Sehlstedt for this useful post:

Purfleet
Avatar
Salim Gul
Member
Members
Level 0
Forum Posts: 37
Member Since:
December 24, 2018
sp_UserOfflineSmall Offline
7
March 7, 2020 - 2:05 am
sp_Permalink sp_Print

Dear Anders Sehlstedt 

you have solved my problem, i was looking for that one, the formula you have posted is very useful not only for me but every finance student, again i am thankful you for give me your expensive time 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
8
March 7, 2020 - 8:17 am
sp_Permalink sp_Print

Hello Salim,

Thank you for your nice feedback.

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Mynda Treacy, Dieneba NDIAYE, Ben Hughes, Dario Serrati, Jay Soysa, Christopher Anderson
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
dectator mang
Francis Drouillard
Orlando Inocente
Jovitha Clemence
Maloxat Axmatovna
Ricardo Freitas
Marko Meglic
Kelly smith
Alan Rushton
Zack Nilsson
Forum Stats:
Groups: 3
Forums: 24
Topics: 6200
Posts: 27181

 

Member Stats:
Guest Posters: 49
Members: 31858
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.