एक्सेल फॉर्मूले के साथ ऋण चुकौती अनुसूची

ऋण अदायगी एक ऋणदाता से पहले उधार ली गई रकम का भुगतान करने का कार्य है, आमतौर पर आवधिक भुगतानों की एक श्रृंखला के माध्यम से जिसमें मूलधन और ब्याज शामिल होते हैं। क्या आप जानते हैं कि आप अपने ऋण अदायगी की गणना के लिए सॉफ्टवेयर प्रोग्राम एक्सेल का उपयोग कर सकते हैं?

यह लेख ऋण की गणना स्थापित करने के लिए एक कदम-दर-चरण मार्गदर्शिका है।

चाबी छीन लेना

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

अपने बंधक को समझना

एक्सेल का उपयोग करके, आप तीन सरल चरणों में अपने बंधक की बेहतर समझ प्राप्त कर सकते हैं। पहला चरण मासिक भुगतान को निर्धारित करता है। दूसरा चरण ब्याज दर की गणना करता है, और तीसरा चरण ऋण अनुसूची निर्धारित करता है।

आप एक्सेल में एक तालिका बना सकते हैं जो आपको ब्याज दर, ऋण की अवधि के लिए ऋण की गणना, ऋण का अपघटन, परिशोधन और मासिक भुगतान बताएगा।

मासिक भुगतान की गणना करें

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

सूत्र, जैसा कि ऊपर स्क्रीनशॉट में दिखाया गया है, निम्नानुसार है:

= -PMT (दर; लंबाई; present_value [future_value]; [प्रकार])

पीएमटी के सामने माइनस साइन जरूरी है क्योंकि फॉर्मूला नेगेटिव नंबर देता है। पहले तीन तर्कों में ऋण की दर, ऋण की अवधि (अवधि की संख्या), और मूल उधार ली गई है। अंतिम दो तर्क वैकल्पिक हैं, अवशिष्ट मूल्य शून्य में चूक करते हैं; अग्रिम में (एक के लिए) या अंत में (शून्य के लिए) भी वैकल्पिक है।

ऋण के मासिक भुगतान की गणना करने के लिए उपयोग किया जाने वाला एक्सेल फॉर्मूला है:

= -पीएमटी ((1 + बी 2) ^ (1/12) -1; बी 4 * 12; बी 3) = पीएमटी ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

स्पष्टीकरण: दर के लिए, हम मासिक दर (अवधि की अवधि) का उपयोग करते हैं, फिर हम अवधियों की संख्या की गणना करते हैं (१० से १० साल के लिए १२ महीने से गुणा) और अंत में, हम मूल उधार को इंगित करते हैं। 10 वर्षों में हमारा मासिक भुगतान $ 1, 161.88 होगा।

वार्षिक ब्याज दर की गणना करें

हमने देखा है कि एक बंधक के लिए मासिक भुगतान की गणना कैसे करें। लेकिन हम एक अधिकतम मासिक भुगतान सेट करना चाहते हैं जो हम वहन कर सकते हैं वह भी उन वर्षों की संख्या प्रदर्शित करता है जिन पर हमें ऋण चुकाना होगा। उस कारण से, हम इसी वार्षिक ब्याज दर को जानना चाहेंगे।

जैसा कि ऊपर स्क्रीनशॉट में दिखाया गया है, हम पहले अवधि (मासिक, हमारे मामले में) और फिर वार्षिक दर की गणना करते हैं। उपयोग किया गया सूत्र RATE होगा, जैसा कि ऊपर दिए गए स्क्रीनशॉट में दिखाया गया है। यह इस प्रकार लिखा जाता है:

= दर (Nper; पीएमटी; present_value [future_value]; [प्रकार])

पहले तीन तर्कों में ऋण की अवधि (अवधि की संख्या), ऋण चुकाने के लिए मासिक भुगतान और मूल उधार लिया गया है। अंतिम तीन तर्क वैकल्पिक हैं, और अवशिष्ट मूल्य शून्य में चूक करते हैं; अग्रिम (एक के लिए) या अंत में (शून्य के लिए) परिपक्वता के प्रबंधन के लिए शब्द तर्क भी वैकल्पिक है। अंत में, अनुमान तर्क वैकल्पिक है, लेकिन दर का प्रारंभिक अनुमान दे सकता है।

उधार देने की दर की गणना करने के लिए उपयोग किया जाने वाला एक्सेल फॉर्मूला है:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

नोट: मासिक भुगतान में संबंधित डेटा को एक नकारात्मक संकेत दिया जाना चाहिए। यही कारण है कि सूत्र से पहले एक ऋण चिह्न है। दर की अवधि 0.294% है।

हम अपने ऋण की वार्षिक दर प्राप्त करने के लिए सूत्र = (१ + बी ५) १२-१ ^ = (१ + ०.२ ९ ४%) ^ १२-१ का उपयोग करते हैं, जो कि ३.५%% है। दूसरे शब्दों में, $ 960 मासिक भुगतान करने के लिए 13 वर्षों में 120, 000 डॉलर उधार लेने के लिए, हमें वार्षिक 3.58% अधिकतम दर पर ऋण पर बातचीत करनी चाहिए।

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

ऋण की लंबाई निर्धारित करना

अब हम देखेंगे कि जब आप वार्षिक दर, मूल उधार लेते हैं और चुकाए जाने वाले मासिक भुगतान को जानते हैं, तो ऋण की लंबाई कैसे निर्धारित करें। दूसरे शब्दों में, हमें 3.10% की दर से $ 120, 000 का बंधक चुकाने की आवश्यकता होगी और $ 100% की मासिक भुगतान राशि>

सूत्र हम उपयोग करेंगे NPER, जैसा कि ऊपर स्क्रीनशॉट में दिखाया गया है, और यह निम्नानुसार लिखा गया है:

= NPER (दर; पीएमटी; present_value [future_value]; [प्रकार])

पहले तीन तर्क ऋण की वार्षिक दर, ऋण चुकाने के लिए आवश्यक मासिक भुगतान और मूल उधार लिया गया है। अंतिम दो तर्क वैकल्पिक हैं, अवशिष्ट मूल्य शून्य में चूक करते हैं। अग्रिम (एक के लिए) या अंत में (शून्य के लिए) देय शब्द भी वैकल्पिक है।

= एनपीईआर ((1 + बी 2) ^ (1/12) -1; -बी 4; बी 3) = एनपीईआर ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

नोट: मासिक भुगतान में संबंधित डेटा को एक नकारात्मक संकेत दिया जाना चाहिए। यही कारण है कि हमारे पास सूत्र से पहले एक ऋण चिह्न है। प्रतिपूर्ति अवधि 127.97 अवधि (हमारे मामले में महीने) है।

हम ऋण चुकौती को पूरा करने के लिए वर्षों की संख्या के लिए सूत्र = B5 / 12 = 127.97 / 12 का उपयोग करेंगे। दूसरे शब्दों में, 3.10% की वार्षिक दर और $ 1, 100 मासिक का भुगतान करने के लिए 120, 000 डॉलर उधार लेने के लिए, हमें 128 महीने या 10 साल और 8 महीने की परिपक्वता चुकानी चाहिए।

ऋण की घोषणा

एक ऋण भुगतान मूलधन और ब्याज से बना होता है। ब्याज की गणना प्रत्येक अवधि के लिए की जाती है - उदाहरण के लिए, 10 वर्षों में मासिक भुगतान हमें 120 अवधि देगा।

ऊपर दी गई तालिका PPMT और IPMT फ़ार्मुलों का उपयोग करते हुए ऋण के टूटने (120 के बराबर कुल अवधि) को दर्शाती है। दो सूत्रों के तर्क समान हैं और निम्नानुसार टूट गए हैं:

= -PPMT (दर; num_period; लंबाई; प्रिंसिपल [अवशिष्ट]; [अवधि])

पीएमटी फार्मूले के लिए तर्क पहले से ही दिखाई दे रहे हैं, "num_period" को छोड़कर, जो उस अवधि को दिखाने के लिए जोड़ा जाता है, जिसमें मूलधन और ब्याज दिए गए ऋण को तोड़ने के लिए। यहाँ एक उदाहरण है:

= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ ^ (1/12) -1; 1; 10 * 12; 120000)

परिणाम का विश्लेषण, "ऋण एक", जो "एक है" अवधि के ऊपर "ऋण अपघटन" के स्क्रीनशॉट में दिखाया गया है; वह है, पहला पीरियड या पहला महीना। हम $ 1, 161.88 का भुगतान $ 856.20 मूलधन और $ 305.68 ब्याज में करते हैं।

एक्सेल में ऋण संगणना

पहले 12 महीनों या पहले 15 महीनों जैसे कई अवधियों के लिए मूलधन और ब्याज चुकौती की गणना करना भी संभव है।

= -CUMPRINC (दर; लंबाई; प्रिंसिपल; प्रारंभ_तिथि; end_date; प्रकार)

हम तर्क, दर, लंबाई, मूलधन और शब्द (जो अनिवार्य हैं) पाते हैं जो हमने पहले भाग में सूत्र PMT के साथ देखा था। लेकिन यहाँ, हमें "start_date" और "end_date" तर्कों की भी आवश्यकता है। "Start_date" का विश्लेषण करने के लिए अवधि की शुरुआत इंगित करता है, और "end_date" का विश्लेषण किए जाने की अवधि के अंत को इंगित करता है।

यहाँ एक उदाहरण है:

= -CUMPRINC ((1 + बी 2) ^ (1/12) -1, बी 4 * 12; बी 3, 1, 12, 0)

परिणाम स्क्रीनशॉट में दिखाया गया है "कमूल 1 वर्ष", इसलिए विश्लेषण की गई अवधि पहली अवधि (12 वें महीने) के पहले अवधि (पहले महीने) के एक से 12 तक होती है। एक वर्ष में, हम मूल में $ 10, 419.55 और ब्याज में $ 3, 522.99 का भुगतान करेंगे।

ऋण का परिशोधन

पूर्व सूत्र हमें समय-समय पर हमारी अनुसूची अवधि बनाने की अनुमति देते हैं, यह जानने के लिए कि हम मूल और ब्याज में मासिक कितना भुगतान करेंगे, और यह जानने के लिए कि कितना भुगतान करना बाकी है।

ऋण अनुसूची बनाना

ऋण अनुसूची बनाने के लिए, हम ऊपर चर्चा किए गए विभिन्न सूत्रों का उपयोग करेंगे और उन्हें अवधि के दौरान विस्तारित करेंगे।

पहली अवधि के कॉलम में, पहली अवधि के रूप में "1" दर्ज करें और फिर सेल को नीचे खींचें। हमारे मामले में, हमें १२० अवधियों की आवश्यकता है क्योंकि १० साल के ऋण भुगतान को १२ महीने के गुणा से १२० के बराबर है।

दूसरा कॉलम वह मासिक राशि है जिसका भुगतान हमें प्रत्येक महीने करने की आवश्यकता है - जो पूरे ऋण अनुसूची पर स्थिर है। राशि की गणना करने के लिए, हमारी पहली अवधि के सेल में निम्नलिखित सूत्र सम्मिलित करें:

= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000;

तीसरा स्तंभ वह मूलधन है जिसे मासिक रूप से चुकाया जाएगा। उदाहरण के लिए, 40 वीं अवधि के लिए, हम प्रति माह 1, 161.88 डॉलर की कुल राशि पर मूल रूप से 945.51 डॉलर का भुगतान करेंगे।

छुड़ाई गई मूल राशि की गणना करने के लिए, हम निम्नलिखित सूत्र का उपयोग करते हैं:

= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

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

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

पांचवें कॉलम में भुगतान करने के लिए शेष राशि है। उदाहरण के लिए, 40 वें भुगतान के बाद, हमें $ 120, 000 पर 83, 994.69 डॉलर का भुगतान करना होगा।

सूत्र इस प्रकार है:

= $ B $ 3 + CUMPRINC (टी.पी.; $ B $ 4 * 12; $ B $ 3, 1, A18; 0)

फार्मूला मूल उधार लेने वाले सेल से आगे की अवधि के तहत प्रिंसिपल के संयोजन का उपयोग करता है। जब हम सेल को कॉपी और ड्रैग करते हैं तो यह अवधि बदलने लगती है। नीचे दी गई तालिका से पता चलता है कि 120 अवधियों के अंत में हमारा ऋण चुकाया गया है।