Excel में अमॉर्टाइज़ेशन शेड्यूल कैसे बनाएँ: चरणों की स्पष्ट व्याख्या

अमॉर्टाइज़ेशन शेड्यूल एक स्पष्ट वित्तीय रोडमैप देता है, जो दिखाता है कि हर लोन किस्त में कितना हिस्सा ब्याज़ का और कितना मूल राशि (प्रिंसिपल) का है, जब तक कि आपका कर्ज़ पूरी तरह चुक न जाए।

Excel के बिल्ट-इन फ़ंक्शंस की मदद से, आप विस्तृत अमॉर्टाइज़ेशन शेड्यूल बना सकते हैं ताकि लोन के पूरे लाइफ़साइकल को विज़ुअलाइज़ किया जा सके, बेहतर वित्तीय निर्णय लिए जा सकें, और जल्दी भुगतान (अर्ली पेऑफ़) के विकल्प भी तलाशे जा सकें।

इस लेख में, आप शुरुआत से Excel में एक संपूर्ण अमॉर्टाइज़ेशन शेड्यूल बनाना सीखेंगे। हम PMT(), IPMT(), और PPMT() जैसे आवश्यक फ़ंक्शंस को कवर करेंगे, आपको चरण-दर-चरण प्रक्रिया से गुज़ारेंगे, और व्यावहारिक परिदृश्यों का अध्ययन करेंगे ताकि आपकी वित्तीय योजना टूलकिट और मज़बूत हो।

अमॉर्टाइज़ेशन शेड्यूल वास्तव में क्या है?

अमॉर्टाइज़ेशन शेड्यूल एक संपूर्ण तालिका है जो समय-समय पर होने वाले लोन भुगतान दिखाती है—हर भुगतान में शामिल मूल राशि और ब्याज़ की रकम—जब तक कि अवधि के अंत में लोन पूरी तरह चुक न जाए।

जब आप लोन लेते हैं, तो कुछ प्रमुख तत्व तय करते हैं कि आपकी किस्तें कैसे काम करेंगी:

• मूल राशि (Principal): उधार ली गई मूल रकम जिसे आपको चुकाना है
• ब्याज़ (Interest): पैसा उधार लेने की लागत, जो मूल राशि के प्रतिशत के रूप में गणना होती है
• भुगतान अवधि: आप कितनी बार भुगतान करते हैं (मासिक, पखवाड़ेवार, आदि)
• लोन अवधि: कुल समय जिसमें आप लोन चुकाएँगे

अधिकांश मानक लोन—जैसे मॉर्गेज, ऑटो लोन, और पर्सनल लोन—में पूरी अवधि के दौरान किस्तें समान रहती हैं। हालांकि, हर भुगतान के साथ ब्याज़ और मूल राशि का अनुपात बदलता है। शुरुआती चरणों में, हर किस्त का बड़ा हिस्सा ब्याज़ में जाता है। जैसे-जैसे आप आगे बढ़ते हैं, प्रत्येक किस्त का अधिक हिस्सा मूल राशि को कम करता है।

अमॉर्टाइज़ेशन शेड्यूल बनाने का प्राथमिक उद्देश्य आपके लोन में पारदर्शिता लाना है। यह आपको दिखाता है कि समय के साथ आप कुल कितना ब्याज़ देंगे, आप कितनी तेज़ी से इक्विटी बना रहे हैं, और अतिरिक्त भुगतान आपके लोन की समयरेखा को कैसे प्रभावित कर सकते हैं। व्यवसायों के लिए, ये शेड्यूल लेखा और कर योजना के उद्देश्यों की भी पूर्ति करते हैं।

वे Excel फ़ंक्शंस और फ़ॉर्मूले जिन्हें आपको जानना चाहिए

Excel में कोई एकल "amortization" फ़ंक्शन नहीं है। इसके बजाय, आप आमतौर पर कुछ विशिष्ट वित्तीय फ़ंक्शंस का उपयोग करके अमॉर्टाइज़ेशन शेड्यूल बनाते हैं, जो मिलकर आपकी किस्तों के अलग-अलग पहलुओं की गणना करते हैं। यहाँ मुख्य फ़ंक्शंस हैं जिनकी ज़रूरत पड़ेगी:

PMT() फ़ंक्शन

PMT() फ़ंक्शन स्थिर किस्तों और स्थिर ब्याज़ दर के आधार पर लोन की भुगतान राशि निकालता है:

=PMT(rate, nper, pv, [fv], [type])

• rate: प्रति अवधि ब्याज़ दर (वार्षिक दर को भुगतान आवृत्ति से विभाजित करें)

• nper: कुल भुगतान अवधियों की संख्या

• pv: वर्तमान मूल्य (लोन की राशि)

• [fv]: वैकल्पिक भविष्य मूल्य (आमतौर पर 0, यदि लोन पूर्ण रूप से चुकाया जा रहा है)

• [type]: वैकल्पिक; 0 अवधि के अंत में भुगतान के लिए, 1 अवधि की शुरुआत में भुगतान के लिए

$250,000 के मॉर्गेज पर 4.5% वार्षिक ब्याज़ के साथ 30 साल के लिए मासिक किस्त निकालने हेतु: 

=PMT(4.5%/12, 30*12, 250000)

यह -$1,266.71 लौटाता है, जो उधारकर्ता के लिए नकद बहिर्वाह को दर्शाता है।

PMT() फ़ंक्शन की विस्तृत व्याख्या, सामान्य त्रुटियाँ, और उन्नत उपयोग उदाहरणों के लिए, हमारे व्यापक PMT() Excel गाइड को देखें।

IPMT() फ़ंक्शन

IPMT() फ़ंक्शन किसी विशिष्ट भुगतान की ब्याज़ राशि की गणना करता है:

=IPMT(rate, per, nper, pv, [fv], [type])

• per: वह विशेष भुगतान अवधि जिसके लिए आप ब्याज़ की गणना कर रहे हैं

per पैरामीटर आवश्यक है क्योंकि यह निर्धारित करता है कि आप किस अवधि के भुगतान का ब्याज़ निकाल रहे हैं—PMT() के विपरीत, जो सभी अवधियों के लिए समान भुगतान देता है, IPMT() का परिणाम चुनी गई अवधि पर काफ़ी निर्भर करता है क्योंकि लोन अवधि के साथ ब्याज़ का हिस्सा घटता है।

यह दिखाता है कि किसी दी गई किस्त में से कितना हिस्सा ब्याज़ में जाता है।

PPMT() फ़ंक्शन

PPMT() फ़ंक्शन किसी विशिष्ट भुगतान की मूल राशि (प्रिंसिपल) के हिस्से की गणना करता है:

=PPMT(rate, per, nper, pv, [fv], [type])

IPMT() की तरह ही, प्रिंसिपल की रकम अवधि के अनुसार बदलती है—शुरुआती किस्तों में प्रिंसिपल का हिस्सा कम और बाद की किस्तों में अधिक होता है। यह दिखाता है कि किसी दी गई किस्त से आपका लोन बैलेंस कितना घटता है।

कस्टम फ़ॉर्मूले

हर भुगतान अवधि के लिए, आप निम्न को ट्रैक करना चाहेंगे:

शेष बैलेंस: हर भुगतान के बाद बकाया लोन राशि

Remaining balance = Previous balance - Current principal payment

संचयी प्रिंसिपल: अब तक चुकाई गई कुल मूल राशि

Cumulative principal = Previous cumulative principal + current principal payment

संचयी ब्याज़: अब तक चुकाया गया कुल ब्याज़

Formula: Previous cumulative interest + current interest payment

Round() फ़ंक्शन

अमॉर्टाइज़ेशन शेड्यूल में छोटे-छोटे राउंडिंग एरर समय के साथ जुड़ सकते हैं:

=ROUND(value, 2)

यह सुनिश्चित करता है कि सभी मौद्रिक मूल्य निकटतम सेंट तक राउंड हो जाएँ।

एब्सोल्यूट बनाम रिलेटिव रेफ़रेंस

अपनी अमॉर्टाइज़ेशन तालिका बनाते समय आपको यह करना होगा:

• ब्याज़ दर और लोन राशि जैसे स्थिर मानों के लिए एब्सोल्यूट रेफ़रेंस ($) का उपयोग करें

• हर अवधि में बदलने वाले मानों के लिए रिलेटिव रेफ़रेंस का उपयोग करें

उदाहरण के लिए, नीचे दिया गया फ़ॉर्मूला ब्याज़ दर, लोन अवधि और राशि के लिए एब्सोल्यूट रेफ़रेंस का उपयोग करता है, लेकिन भुगतान अवधि के लिए रिलेटिव रेफ़रेंस:

=PPMT($B$2/12, A10, $B$3*12, $B$1)

अमॉर्टाइज़ेशन शेड्यूल बनाने की चरण-दर-चरण गाइड

अब जब आप प्रमुख फ़ंक्शंस समझ चुके हैं, तो चलिए शुरुआत से एक अमॉर्टाइज़ेशन शेड्यूल बनाते हैं। यह वॉकथ्रू एक संपूर्ण लोन भुगतान तालिका बनाएगा जो आपके लोन के जीवनकाल में हर किस्त को ट्रैक करेगी।

1. लोन डेटा दर्ज करना

नीचे दिए गए उदाहरण के अनुसार समर्पित इनपुट सेल्स में अपने लोन विवरण दर्ज करें:

• लोन राशि: $250,000 (सेल B1)
• वार्षिक ब्याज़ दर: 4.5% (सेल B2)
• लोन अवधि: 30 वर्ष (सेल B3)
• भुगतान आवृत्ति: मासिक (सेल B4)

लोन इनपुट पैरामीटर्स सेट करना। चित्र: लेखक द्वारा। 

2. मासिक किस्त की गणना

सेल B5 में, PMT फ़ंक्शन का उपयोग करके मासिक किस्त निकालें: =PMT(B2/12, B3*12, B1)

यह फ़ॉर्मूला -$1,266.71 लौटाता है, जो आपकी मासिक किस्त को दर्शाता है। नकारात्मक चिन्ह बताता है कि यह भुगतान (नकद बहिर्वाह) है।

कॉलम हेडर और फ़ॉर्मूले सेट करना। चित्र: लेखक द्वारा।

3. शेड्यूल की पहली पंक्ति बनाना

अगले चरण में, हम निम्न हेडर बना सकते हैं और पहले डेटा रो (पंक्तियाँ 7 और 8) पर काम कर सकते हैं:  

• अवधि (Period): सेल A8 में 1 दर्ज करें

• भुगतान राशि (Payment Amount): सेल B8 में =$B$5 (गणना की गई मासिक किस्त का संदर्भ)

• ब्याज़ भुगतान (Interest Payment): सेल C8 में =IPMT($B$2/12, A8, $B$3*12, $B$1)

• प्रिंसिपल भुगतान (Principal Payment): सेल D8 में =PPMT($B$2/12, A8, $B$3*12, $B$1)

• शेष बैलेंस (Remaining Balance): सेल E8 में =$B$1+D8 (मूल लोन प्लस प्रिंसिपल भुगतान—यह इसलिए काम करता है क्योंकि D8 नकारात्मक मान देता है, जिससे लोन से प्रिंसिपल घट जाता है)

• संचयी प्रिंसिपल (Cumulative Principal): सेल F8 में =D8 (पहली पंक्ति के लिए प्रिंसिपल भुगतान के समान)

• संचयी ब्याज़ (Cumulative Interest): सेल G8 में =C8 (पहली पंक्ति के लिए ब्याज़ भुगतान के समान)

पहले भुगतान का विस्तृत विभाजन दिखाते हुए। चित्र: लेखक द्वारा।

4. शेड्यूल को पूरा करना

दूसरी पंक्ति (पंक्ति 9) के लिए:

• अवधि (Period): सेल A9 में =A8+1 (1 से बढ़ाएँ)

• भुगतान राशि (Payment Amount): B8 का फ़ॉर्मूला B9 में कॉपी करें

• ब्याज़ भुगतान (Interest Payment): सेल C9 में =IPMT($B$2/12, A9, $B$3*12, $B$1)

• प्रिंसिपल भुगतान (Principal Payment): सेल D9 में =PPMT($B$2/12, A9, $B$3*12, $B$1)

• शेष बैलेंस (Remaining Balance): सेल E9 में =E8+D9 (पिछला बैलेंस प्लस वर्तमान प्रिंसिपल)

• संचयी प्रिंसिपल (Cumulative Principal): सेल F9 में =F8+D9 (पिछली कुल राशि प्लस वर्तमान प्रिंसिपल)

• संचयी ब्याज़ (Cumulative Interest): सेल G9 में =G8+C9 (पिछली कुल राशि प्लस वर्तमान ब्याज़)

ध्यान दें कि IPMT() और PPMT(), दोनों में A9 रिलेटिव रेफ़रेंस (डॉलर साइन के बिना) है, यानी जब आप फ़ॉर्मूला नीचे कॉपी करेंगे, तो यह अपने-आप A10, A11, आदि में अपडेट होता जाएगा और हर बार सही अवधि की गणना करेगा।

ऐसे फ़ॉर्मूले सेट करना जिन्हें नीचे तक कॉपी किया जा सके। चित्र: लेखक द्वारा।

अमॉर्टाइज़ेशन शेड्यूल में शुरुआती कई भुगतान। चित्र: लेखक द्वारा।

पंक्ति 9 की सभी सेल्स चुनें और 30-वर्षीय मासिक लोन (360 भुगतान) के लिए पंक्ति 367 तक ड्रैग करें।

जैसे-जैसे लोन अवधि बढ़ती है, हर भुगतान का अधिक हिस्सा ब्याज़ के बजाय प्रिंसिपल में जाता है। चित्र: लेखक द्वारा।

5. अंतिम भुगतान का सत्यापन

सही तरह से बने अमॉर्टाइज़ेशन शेड्यूल में अंतिम भुगतान शेष बैलेंस को बिल्कुल शून्य पर ले आता है, और संचयी प्रिंसिपल मूल लोन राशि के बराबर होता है।

अंतिम भुगतान शेष बैलेंस को बिल्कुल शून्य पर लाता है। चित्र: लेखक द्वारा।

जैसा कि आप अंतिम पंक्ति में देख सकते हैं, शेष राशि $0.00 है (हाइलाइटेड), और संचयी प्रिंसिपल $250,000 के बराबर है, जो हमारी मूल लोन राशि से मेल खाता है। यह पुष्टि करता है कि हमारा अमॉर्टाइज़ेशन शेड्यूल सटीक है।

अंतिम बैलेंस की पुष्टि करते समय, प्रमुख गणनाओं पर ROUND() फ़ंक्शन लगाना उपयोगी हो सकता है। Excel की गणना सटीकता के कारण छोटे दशमलव अंतर अंतिम बैलेंस को शून्य के बजाय बहुत छोटी अवशिष्ट राशि दिखा सकते हैं। =ROUND(value, 2) का उपयोग यह सुनिश्चित करता है कि सभी मौद्रिक मानक सामान्य वित्तीय रिपोर्टिंग मानकों के अनुरूप रहें।

अतिरिक्त बातें जिन पर आप विचार करना चाहेंगे

मानक शेड्यूल एक शुरुआती बिंदु है। अपने मॉडल को अधिक लचीला बनाने के लिए इन बातों पर विचार करें:

भुगतान में विविधताओं का प्रबंधन

अलग-अलग भुगतान परिदृश्यों का पता लगाने के लिए अनुकूलित किए जाने पर अमॉर्टाइज़ेशन शेड्यूल और भी मूल्यवान हो जाते हैं। अतिरिक्त भुगतान मॉडल करने के लिए, अनुपूरक भुगतानों के लिए एक अलग इनपुट सेल बनाएँ और अपने प्रिंसिपल भुगतान फ़ॉर्मूले में इस राशि को शामिल करें। यह तरीका दिखाता है कि अतिरिक्त भुगतान कैसे लोन अवधि और कुल चुकाए गए ब्याज़ दोनों को घटाते हैं। बलून भुगतान या परिवर्तनीय ब्याज़ दर वाले लोन के लिए, अपने शेड्यूल में अलग-अलग सेक्शन बनाएँ जो विशिष्ट समय पर दर परिवर्तनों को दर्शाएँ। अलग-अलग दर परिवेश और उनके कुल लागत पर प्रभाव की तुलना करने के लिए आप Excel के Scenario Manager का उपयोग कर सकते हैं।

पढ़ने में आसानी और विश्लेषण को बेहतर बनाना

सही फ़ॉर्मेटिंग आपके अमॉर्टाइज़ेशन शेड्यूल को एक साधारण तालिका से एक सूचनाप्रद वित्तीय उपकरण में बदल देती है। सभी मौद्रिक मूल्यों पर समान दशमलव स्थानों के साथ मुद्रा फ़ॉर्मेट लागू करें, और कंडीशनल फ़ॉर्मेटिंग का उपयोग कर प्रमुख मील के पत्थरों को हाइलाइट करें—जैसे जब आपने आधा प्रिंसिपल चुका दिया हो या विशिष्ट इक्विटी सीमा प्राप्त की हो। यह समझना भी महत्वपूर्ण है कि अलग-अलग ब्याज़ अर्जन (accrual) विधियाँ आपकी किस्तों को कैसे प्रभावित करती हैं। डेली एक्रुअल (जो छात्र लोन में सामान्य है) 365-दिवसीय वर्ष के आधार पर ब्याज़ की गणना करता है, जबकि कई मॉर्गेज मासिक एक्रुअल विधि का उपयोग करते हैं। आपका शेड्यूल कौन-सी विधि उपयोग करता है, इसका दस्तावेज़ीकरण करना वास्तविक लोन स्टेटमेंट्स से तुलना करते समय सटीकता सुनिश्चित करता है।

विशिष्ट लोन प्रकारों के लिए अनुकूलन

विभिन्न लोन की विशिष्ट विशेषताएँ होती हैं जिनके लिए आपके बेसिक अमॉर्टाइज़ेशन टेम्पलेट में समायोजन की आवश्यकता हो सकती है। एस्क्रो खातों वाले मॉर्गेज के लिए, प्रिंसिपल और ब्याज़ के साथ-साथ बीमा और कर घटकों को ट्रैक करने हेतु कॉलम जोड़ें। नेगेटिव अमॉर्टाइज़ेशन (जब भुगतान संचित ब्याज़ को कवर नहीं करते) वाले लोन के लिए, अपने शेष बैलेंस की गणना को ऐसे संशोधित करें कि बिना चुकाया ब्याज़ प्रिंसिपल में जोड़ दिया जाए। Excel के वित्तीय फ़ंक्शंस में भुगतान समय विकल्प (type पैरामीटर) आपको अवधि की शुरुआत या अंत में भुगतान मॉडल करने देता है, जो विशेष रूप से लीज़ और कुछ वाणिज्यिक लोन के लिए उपयोगी है जहाँ समय संबंधी परंपराएँ मानक उपभोक्ता लोन से भिन्न होती हैं।

निष्कर्ष

Excel में अमॉर्टाइज़ेशन शेड्यूल बनाना आपको आपके लोन के बारे में उन बातों की समझ देता है जो अधिकांश ऋणदाता नहीं देते। अपना शेड्यूल बनाकर, आप देख सकते हैं कि हर भुगतान आपके बैलेंस को कैसे प्रभावित करता है, समय के साथ उधारी की वास्तविक लागत समझ सकते हैं, और प्रीपेमेंट विकल्पों पर सूचित निर्णय ले सकते हैं। इस प्रक्रिया से विकसित कौशल—Excel के वित्तीय फ़ंक्शंस का उपयोग, डायनेमिक फ़ॉर्मूले बनाना, और सूचनाप्रद तालिकाएँ डिज़ाइन करना—लोन विश्लेषण से कहीं आगे तक जाते हैं और व्यक्तिगत व व्यावसायिक वित्तीय योजना के कई पहलुओं में लागू होते हैं।

यदि आप अमॉर्टाइज़ेशन से आगे बढ़कर अधिक संपूर्ण वित्तीय मॉडल बनाना चाहते हैं—जिनमें आय का पूर्वानुमान, निवेश का मूल्यांकन, और What-if परिदृश्य चलाना शामिल है—तो हमारा Financial Modeling in Excel कोर्स देखें। यह वित्तीय विश्लेषण के लिए आपके स्प्रेडशीट कौशल को मज़बूत करने का एक उत्कृष्ट अगला कदम है। याद रखें, जितना अधिक आप इन वित्तीय गणनाओं में सहज होते जाएँगे, उतनी ही बेहतर स्थिति में आप अपने वित्तीय भविष्य को नियंत्रित कर पाएँगे—चाहे आप व्यक्तिगत कर्ज़ प्रबंधन कर रहे हों, व्यावसायिक निवेश की योजना बना रहे हों, या ग्राहकों को उनके वित्तीय विकल्पों पर सलाह दे रहे हों।